JDBC:java 数据库连接,一般分为六个步骤:
1、加载数据库提供商提供的驱动
2、获取数据库连接对象
3、获取处理命令
4、执行sql语句
5、处理执行结果
6、回收资源
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class JDBCDemo { public static void main(String[] args) throws ClassNotFoundException, SQLException { // 1、加载数据库提供商提供的驱动 Class.forName("com.mysql.jdbc.Driver"); // 2、获取数据库连接对象 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","123456"); // 3、获取处理命令 Statement stmt = conn.createStatement(); // 4、执行sql语句 boolean b = stmt.execute("select * from mytable"); // 5、处理执行结果 System.out.println(b); // 6、回收资源 stmt.close(); conn.close();使用上述Java语句在真实使用时会用一定隐患:sql注入,而为防止sql注入我们使用预处理命令
PreparedStatement ps = conn.prepareStatement("select * from mytable");而一般的sql语句分为更新和查询两种,同样的执行语句也分为两种如:
当sql语句为更新语句,即增、删、改时的执行语句如下:
int i = ps.executeUpdate();当sql语句为查询语句时的操作如下:
ResultSet rs = ps.executeQuery();为更好的使用jdbc,我们简单封装一下jdbc,如下:
public class DBConnection { private static String DRIVER; private static String URL; private static String USER ; private static String PASSWORD ; //在静态块中加载驱动,防止反复加载 static{ try { //获取属性对象 Properties props = System.getProperties(); //加载指定属性文件,根据属性名获取属性值 props.load(new FileInputStream("src/jdbc.properties")); DRIVER = props.getProperty("driver"); URL = props.getProperty("url"); USER = props.getProperty("user"); PASSWORD = props.getProperty("password"); Class.forName(DRIVER); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //获取连接对象 public static Connection getConn(){ try { return DriverManager.getConnection(URL, USER, PASSWORD); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } //资源回收 public static void close(ResultSet rs,PreparedStatement ps,Connection conn){ try { if(rs != null) rs.close(); if(ps != null) ps.close(); if(conn != null) conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ DBConnection.close(null, ps, conn); } } } 我们看到对上述的代码块,并没有对于获取执行命令,执行sql语句和处理执行结果进行封装,这是因为我们对查询的sql语句不明确,于是我们有了如下处理: public class DAOHelper { public static boolean execUpdate(Connection conn,String sql,Object ... objs){ PreparedStatement ps = null; try { ps = conn.prepareStatement(sql); for (int i = 0; i < objs.length; i++) { ps.setObject(i+1, objs[i]); } int i = ps.executeUpdate(); return i > 0 ? true : false; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return false; } public static <T> List<T> queryList(String sql,CallBack<T> call,Object ... objs){ Connection conn = DBConnection.getConn(); PreparedStatement ps = null; try { ps = conn.prepareStatement(sql); for (int i = 0; i < objs.length; i++) { ps.setObject(i+1, objs[i]); } ResultSet rs = ps.executeQuery(); return call.getDatas(rs); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } public static <T> T queryOne(String sql,CallBack<T> call,Object ... objs){ Connection conn = DBConnection.getConn(); PreparedStatement ps = null; try { ps = conn.prepareStatement(sql); for (int i = 0; i < objs.length; i++) { ps.setObject(i+1, objs[i]); } ResultSet rs = ps.executeQuery(); return call.getData(rs); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } public static abstract class CallBack<T>{ public List<T> getDatas(ResultSet rs){ return null; } public T getData(ResultSet rs) { return null; } } } 其中对于抽象类CallBack<T>,使用jdk1.8版本的可以使用接口的方法:
public interface CallBack<T>{ default List<T> getDatas(ResultSet rs){ return null; } default T getData(ResultSet rs){ return null; } }