JDBCUtil编写心得
详细代码
//操作数据库公共类
public class BaseDao {
private static String driver;
private static String username;
private static String password;
private static String url;
private static Connection connection=null;
private static PreparedStatement preparedStatement=null;
private static ResultSet resultSet=null;
//静态代码块,类加载的时候就可以初始化
static {
//创建一个配置类
Properties properties = new Properties();
//通过类加载器读取配置文件内容转化为流
InputStream resourceAsStream = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
try {
properties.load(resourceAsStream);//从流中读取信息到properties对象中
} catch (IOException e) {
e.printStackTrace();
}
driver=properties.getProperty("driver");
username=properties.getProperty("username");
password=properties.getProperty("password");
url=properties.getProperty("url");
}
//获取数据库的链接
public static Connection getConnection(){
Connection connection=null;
try {
Class.forName(driver);//利用反射加载mysql-connector-java驱动
connection = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
//编写查询公共类
public static ResultSet execute(String sql,Object[] params) throws SQLException {
connection=getConnection();//获取链接
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
//setObject占位符从1开始,但是params数组是从0开始的
preparedStatement.setObject(i+1,params[i]);//根据传入的params参数查询数据,会得到一个包含结果集的resultSet对象如下方所示
}
resultSet = preparedStatement.executeQuery();//采用了preparedStatement预编译方式故此处不必传参
//closeResource(connection,preparedStatement,resultSet);
return resultSet;
}
//编写增删改公共方法
public static int Upexecute(String sql,Object[] params) throws SQLException {
connection=getConnection();//获取链接
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
//setObject占位符从1开始,但是params数组是从0开始的
preparedStatement.setObject(i+1,params[i]);//根据出入的params参数插入,更新,或是删除数据,此操作只返回一个int参数
}
int UpdateresultSet = preparedStatement.executeUpdate();//采用了preparedStatement预编译方式故此处不必传参
//closeResource(connection,preparedStatement,resultSet);
return UpdateresultSet;
}
//释放资源
public static boolean closeResource(){
boolean flage=true;
if (resultSet != null) {
try {
resultSet.close();
//GC回收
resultSet=null;
System.out.println("rsclose");
} catch (SQLException e) {
e.printStackTrace();
flage=false;
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
//GC回收
preparedStatement=null;
System.out.println("psclose");
} catch (SQLException e) {
e.printStackTrace();
flage=false;
}
}
if (connection != null) {
try {
connection.close();
//GC回收
connection=null;
System.out.println("connectionclose");
} catch (SQLException e) {
e.printStackTrace();
flage=false;
}
}
return flage;
}
}
结论
这种写法存在一个问题,没有考虑到事务的处理,将一些数据库链接的重要环节写死了,直接在方法中开启了数据库链接和预编译操作,不能进行手动开合,
private static Connection connection=null;
private static PreparedStatement preparedStatement=null;
private static ResultSet resultSet=null;
推荐写法
public class BaseDao {
private static String driver;
private static String url;
private static String userName;
private static String password;
static {
Properties properties = new Properties();
InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
try {
properties.load(is);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
userName = properties.getProperty("username");
password = properties.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection con = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, userName, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
public static ResultSet execute(Connection con, PreparedStatement ps, ResultSet rs, String sql, Object[] params) throws SQLException {
ps = con.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
//setObject 占位符下标从1开始,我们数组是从0开始
ps.setObject(i + 1, params[i]);
}
rs = ps.executeQuery();
return rs;
}
public static int execute(Connection con, PreparedStatement ps, String sql, Object[] params) throws SQLException {
ps = con.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
//setObject 占位符下标从1开始,我们数组是从0开始
ps.setObject(i + 1, params[i]);
}
return ps.executeUpdate();
}
public static boolean closeResource(Connection con, PreparedStatement ps, ResultSet rs) {
boolean closeFlag = true;
if (rs != null) {
try {
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();
closeFlag = false;
}
}
if (ps != null) {
try {
ps.close();
ps = null;
} catch (SQLException e) {
e.printStackTrace();
closeFlag = false;
}
}
if (con != null) {
try {
con.close();
con = null;
} catch (SQLException e) {
e.printStackTrace();
closeFlag = false;
}
}
return closeFlag;
}
public static boolean closeResource(Connection con) {
boolean closeFlag = true;
if (con != null) {
try {
con.close();
con = null;
} catch (SQLException e) {
e.printStackTrace();
closeFlag = false;
}
}
return closeFlag;
}
public static boolean closeResource(PreparedStatement ps, ResultSet rs) {
boolean closeFlag = true;
if (rs != null) {
try {
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();
closeFlag = false;
}
}
if (ps != null) {
try {
ps.close();
ps = null;
} catch (SQLException e) {
e.printStackTrace();
closeFlag = false;
}
}
return closeFlag;
}
}
本文来自博客园,作者:Cn_FallTime,转载请注明原文链接:https://www.cnblogs.com/CnFallTime/p/16002010.html