以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(); } } } } }