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; } }