目录
1. 所需jar包:
<!-- mysql 数据库依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
<!-- 添加日志相关的jar包 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.25</version>
</dependency>
<!-- log end -->
2. jdbcUtil接口
package com.qtao.spring.common.jdbc;
import java.sql.ResultSet;
public interface JdbcUtil {
/**
* 查询
* @param sql
* @return
*/
ResultSet query(String sql);
/**
* 修改
* @param sql
* @return
*/
boolean update(String sql);
/**
* 关闭数据库连接(查询时务必关闭)
*/
void closeConnection();
}
3. jdbcUtil接口实现类
package com.qtao.spring.common.jdbc;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.*;
/**
* @ClassNames JdbcUtilImpl
* @Description 此实现类仅支持mysql数据库
* @Author qtao
* @Date 2019/8/27 11:19
* Version 1.0
**/
public class JdbcUtilImpl implements JdbcUtil {
private Logger logger = LoggerFactory.getLogger(this.getClass());
private String mysqlDriverClass = "com.mysql.jdbc.Driver";
private String url_1 = "jdbc:mysql://";
private String url_2 = "?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull";
// 数据库IP
private String database_ip = "192.168.159.51";
// 数据库端口
private String database_port = "3306";
// 数据库名称
private String database_name = "ssm_demo";
// 数据库名称
private String username = "root";
// 数据库密码
private String password = "root";
private Connection con;
private PreparedStatement psm;
private ResultSet rs;
/**
* 获取数据库连接
*
* @return
*/
private Connection getConnection() {
try {
Class.forName(this.mysqlDriverClass);
String url = url_1 + database_ip + ":" + database_port + "/" + database_name + url_2;
this.con = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
logger.error("获取数据库连接驱动失败,ClassNotFoundException={}", e);
} catch (SQLException e) {
logger.error("连接数据库失败,SQLException={}", e);
}
return con;
}
/**
* 对SQL进行预编译
*
* @param sql
*/
private void getPreparedStatement(String sql) {
try {
psm = con.prepareStatement(sql);
} catch (SQLException e) {
logger.error("对SQL进行预编译失败,SQLException={}", e);
}
}
/**
* 关闭数据库连接
*/
public void closeConnection() {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
logger.error("关闭ResultSet连接失败,Exception={}", e);
}
try {
if (psm != null) {
psm.close();
}
} catch (SQLException e) {
logger.error("关闭PreparedStatement连接失败,Exception={}", e);
}
try {
if (con != null) {
con.close();
}
} catch (SQLException e) {
logger.error("关闭Connection连接失败,Exception={}", e);
}
}
/**
* 查询方法
*
* @param sql
* @param obj
* @return
*/
public ResultSet query(String sql) {
getConnection();
getPreparedStatement(sql);
try {
rs = psm.executeQuery();
} catch (SQLException e) {
logger.error("查询时出现失败,SQLException={}", e);
}
return rs;
}
public boolean update(String sql) {
getConnection();
getPreparedStatement(sql);
int num = 0;
try {
num = psm.executeUpdate();
} catch (SQLException e) {
logger.error("新增/更新/删除时出现失败,SQLException={}", e);
} finally {
closeConnection();
}
if (num > 0) {
return true;
}
return false;
}
}
4. 示例
public static void main (String[] args){
JdbcUtil jdbcUtil = new JdbcUtilImpl();
String sql = "select * from USER";
ResultSet rs = jdbcUtil.query(sql);
List<User> list = new ArrayList<>();
try {
while (rs.next()) {
User user = new User();
user.setId(rs.getLong("ID"));
user.setName(rs.getString("NAME"));
user.setEmail(rs.getString("EMAIL"));
user.setPassword(rs.getString("PASSWORD"));
user.setRemark(rs.getString("REMARK"));
list.add(user);
}
} catch (Exception e){
logger.error("异常,Exception={}",e);
} finally {
jdbcUtil.closeConnection();
}
for (User user : list){
System.out.println("编号:"+user.getId());
System.out.println("昵称:"+user.getName());
System.out.println("邮箱:"+user.getEmail());
System.out.println("备注:\n\t"+user.getRemark());
}
}
结果:
本文如果对您有所帮助记得点个赞哦 ^_^