package demo; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public final class JDBCUtils { private JDBCUtils(){}//因为是工具类,所以不需要new对象(因为全部是静态方法) private static String driverName = "com.mysql.jdbc.Driver"; private static String url = "jdbc:mysql://localhost/db_test"; private static String userName = "root"; private static String password = "sjjhong"; // 注册驱动放在静态代码块中,保证只注册一次,当类装载到虚拟机中就会执行----->不用显式调用 static { try { Class.forName(driverName); } catch (ClassNotFoundException e) { // TODO 自动生成的 catch 块 throw new ExceptionInInitializerError();//初始化失败 } } public static Connection getConnection() throws SQLException { //建立连接 return DriverManager.getConnection(url,userName,password); } public static void free(ResultSet rs,Statement stat,Connection conn) { try { if(rs != null)//否则可能会抛空指针异常 rs.close(); rs = null; } catch (SQLException e) { e.printStackTrace(); } finally { try { if(stat != null) stat.close(); stat = null; } catch (SQLException e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } finally { try { if(conn != null) conn.close(); conn = null; } catch (SQLException e) { e.printStackTrace(); } } } } }
package demo; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public final class SingDemo { private static String driverName = "com.mysql.jdbc.Driver"; private static String url = "jdbc:mysql://localhost/db_test"; private static String userName = "root"; private static String password = "sjjhong"; private SingDemo(){} private static SingDemo sin = null;//这里用的是懒加载的模式(最好使用饿汉式,安全方便) public static SingDemo getInstance()//获取实例 { if(sin != null) { synchronized (SingDemo.class)//获取锁定,处理并发操作 { if(sin != null) { sin = new SingDemo(); } } } return sin; } static { try { Class.forName(driverName); } catch (ClassNotFoundException e) { throw new ExceptionInInitializerError(); } } public Connection getConnection() throws SQLException { return DriverManager.getConnection(url,userName,password); } public void free(ResultSet rs,Statement stat,Connection conn) { try { if(rs != null)//否则会抛空指针异常 rs.close(); rs = null; } catch (SQLException e) { e.printStackTrace(); } finally { try { if(stat != null) stat.close(); stat = null; } catch (SQLException e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } finally { try { if(conn != null) conn.close(); conn =null; } catch (SQLException e) { e.printStackTrace(); } } } } }
package biogDemo; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class DBUtils { private static String driverName = null; private static String user = null; private static String url = null; private static String password = null; private static DBUtils instance = null; public static DBUtils getInstance() { if(instance == null) { synchronized (DBUtils.class) { if(instance == null) { instance = new DBUtils(); } } } return instance; } private DBUtils() { try { Properties prop = new Properties(); prop.load(new FileInputStream(getPropertyFilePath()));//载入配置文件 //读取配置文件并赋值 driverName = prop.getProperty("driverName"); user = prop.getProperty("user"); url = prop.getProperty("url"); password = prop.getProperty("password"); Class.forName(driverName); } catch (ClassNotFoundException e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } catch (FileNotFoundException e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } catch (IOException e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } } public String getPropertyFilePath() { StringBuilder sb = new StringBuilder(); sb.append(System.getProperty("user.dir"));//当前路径 sb.append("\\src\\").append("biogDemo\\").append("db.properties"); return sb.toString(); } public Connection getConnection() throws SQLException { return DriverManager.getConnection(url, user, password); } public static void free(Connection conn,Statement stat,ResultSet rs) { try { if(rs != null) { rs.close(); } rs = null; } catch (SQLException e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } finally { try { if(stat != null) { stat.close(); } stat = null; } catch (SQLException e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } finally { try { if(conn != null) { conn.close(); } conn = null; } catch (SQLException e) { e.printStackTrace(); } } } } }
其中,db.properties文件中写入如下内容:
driverName = com.mysql.jdbc.Driver password = sjjhong url = jdbc:mysql://localhost/test user = root
package biogDemo; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import demo.JDBCUtils; /** * @author Rowand jj * */ public class Main { public static void main(String[] args) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; String sql = "select id,name,birthday,money from tb_9 where id> ?"; int ID = 2; try { conn = JDBCUtils.getConnection();//建立连接 ps = conn.prepareStatement(sql); ps.setInt(1,ID);//设置sql语句中的通配符(?)为ID //ResultSetMetaData可用于获取关于 ResultSet 对象中列的类型和属性信息的对象 ResultSetMetaData rsmd = ps.getMetaData(); int COUNT = rsmd.getColumnCount();//获取列数 rs = ps.executeQuery(); //执行查询 while(rs.next()) { for(int i = 1;i <= COUNT;i++) { System.out.print(rs.getObject(i) + " "); } System.out.println("\n"); } } catch(SQLException e) { throw new RuntimeException("查询失败!"); } finally { // 释放,你懂得。别忘了! JDBCUtils.free(rs, ps, conn); } } }
每次执行sql语句, 数据库都要执行sql语句的编译,最好用于仅执行一次查询并返回结果的情形,效率高于PreparedStatement。
PreparedStatement:(用于执行带或不带 IN 参数的预编译 SQL 语句)
1.执行的SQL语句中是可以带参数的,并支持批量执行SQL。由于采用Cache机制,则预先编译的语句,就会放在Cache中,下次执行相同SQL语句时,则可以直接从Cache中取出来,故相同操作批量数据效率较高。