[JDBC-1] JDBC Base Template

以Statement建立的标准模板：

static void template() throws Exception {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// //1.注册驱动。实际使用中应当去掉该部分，因为工具类已实现这部分代码。
			// Class.forName("com.mysql.jdbc.Driver");
			// 2.建立连接
			conn = JdbcUtils.getConnection();
			// conn = JdbcUtilsSing.getInstance().getConnection();
			// 3.创建语句
			st = conn.createStatement();

			// 4.执行语句
			rs = st.executeQuery("select * from contacts");

			// 5.处理结果
			while (rs.next()) {
				// 参数中的1,2,3,4是指sql中的列索引
				System.out.println(rs.getObject(1) + "\t" + rs.getObject(2)
						+ "\t" + rs.getObject(3) + "\t" + rs.getObject(4)
						+ "\t" + rs.getObject(5) + "\t" + rs.getObject(6)
						+ "\t" + rs.getObject(7) + "\t" + rs.getObject(8)
						+ "\t" + rs.getObject(9));
			}
		} finally {
			JdbcUtils.free(rs, st, conn);
		}

	}

 

以PreparedStatement建立的Read模板　　

//带有参数，尤其是String类型，一定要使用PreparedStatement
	static void read(String name) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 2.建立连接
			conn = JdbcUtils.getConnection();

			// conn = JdbcUtilsSing.getInstance().getConnection();
			// 3.创建语句
			//sql语句使用拼接字符串的方式会引起SQL注入的安全问题。引入prepareStatement，取代statement来解决这个问题
			String sql = "select id, name,birthday  from contacts where name=?";
			//String sql = "select id, name, birthday  from contacts where name='" + name + "'";
			//Statement st = conn.createStatement(sql1);
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			// 4.执行语句
			//注意这里没有参数sql
			rs = ps.executeQuery();

			// 5.处理结果
			while (rs.next()) {
				System.out.println(rs.getInt("id") + "\t"
						+ rs.getString("name") + "\t" + rs.getDate("birthday"));
			}

		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}

　　

建立连接实例的工具类：

package com.amuos.jdbc.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 
 * 2015-1-24
 * 
 * @author <a href="472846889@qq.com">王娟</a>
 * 
 */
//使用final关键字，不允许继承
public final class JdbcUtils {
    private static String url = "jdbc:mysql://localhost:3306/jdbc";
    private static String user = ""; //请填入数据库用户名
    private static String password = ""; //请填入数据库密码
//构造函数私有化，不允许new
    private JdbcUtils() {
    }
//保证注册驱动只执行一次.静态代码块只在类装载到虚拟机的时候执行一次。
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    public static void free(ResultSet rs, Statement st, Connection conn) {
        try {
            if (rs != null)
                rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (st != null)
                    st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (conn != null)
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
            }
        }
    }
}

 

工具类的单例模式：

package com.amuos.jdbc.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 
 * 2015-1-24  单例模式
 * 
 * @author <a href="472846889@qq.com">王娟</a>
 * 
 */
public final class JdbcUtilsSing {
    private String url = "jdbc:mysql://localhost:3306/jdbc";
    private String user = "";//请输入数据库用户名
    private String password = "";//请输入数据库密码

    // private static JdbcUtilsSing instance = new JdbcUtilsSing();
    private static JdbcUtilsSing instance = null;

    private JdbcUtilsSing() {
    }

    //延迟初始化。加锁
    public static JdbcUtilsSing getInstance() {
        if (instance == null) {
            synchronized (JdbcUtilsSing.class) {
                if (instance == null) {
                    instance = new JdbcUtilsSing();
                }
            }
        }
        return instance;
    }

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    public void free(ResultSet rs, Statement st, Connection conn) {
        try {
            if (rs != null)
                rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (st != null)
                    st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (conn != null)
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
            }
        }
    }
}