Every time you use jdbc, you have to search it in the search engine, so why not write a blog directly?
1. JDBC
1. Use Maven to import dependencies
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.26</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.13.RELEASE</version>
</dependency>
2. The structure diagram is as follows
3.BaseDao.java
package com.it.www.util;
import java.sql.*;
/**
* @projectName: spring_demo01
* @package: com.it.www.util
* @className: MybatisUtil
* @author: Sun
* @description: TODO
* @date: 2023/5/12 17:49
* @version: 1.0
*/
public class BaseDao {
protected Connection conn = null;
protected PreparedStatement pstmt = null;
protected ResultSet resultSet = null;
static {
try {
Class.forName(ConfigerManager.getInstance().getValue("driver"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// Get a connection to the database
private boolean getConn() {
try {
conn = DriverManager.getConnection(ConfigerManager.getInstance().getValue("url"), ConfigerManager.getInstance().getValue("username"), ConfigerManager.getInstance().getValue("password"));
} catch (SQLException e) {
e.printStackTrace();
}
if (conn != null) {
return true;
}
return false;
}
//Encapsulate a method to perform updates -- add, delete, modify
protected int executeUpdate(String sql, Object... params) throws SQLException {
int rows = 0;
try {
if (getConn()) {
pstmt = conn. prepareStatement(sql);
//Bind data to the placeholder in sql
if (params != null & amp; & amp; params. length > 0) {
for (int i = 0; i < params. length; i ++ ) {
pstmt.setObject(i + 1, params[i]);
}
}
// execute the operation
rows = pstmt. executeUpdate();
}
} catch (SQLException e) {
e.printStackTrace();
throw e;
}
return rows;
}
//Encapsulate a method to execute a query --query
protected ResultSet executeQuery(String sql, Object... params) throws SQLException {
try {
if (getConn()) {
pstmt = conn. prepareStatement(sql);
//Bind data to the placeholder in sql
if (params != null & amp; & amp; params. length > 0) {
for (int i = 0; i < params. length; i ++ ) {
pstmt.setObject(i + 1, params[i]);
}
}
resultSet = pstmt. executeQuery();
}
} catch (SQLException e) {
e.printStackTrace();
throw e;
}
return resultSet;
}
//Define a method to close the resource
protected void closeResource(){
if(resultSet!=null){
try {
resultSet. close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(pstmt!=null){
try {
pstmt. close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn. close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
4. Configer Manager
package com.it.www.util;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
/**
* @projectName: spring_demo01
* @package: com.it.www.util
* @className: DriverManager
* @author: Sun
* @description: TODO
* @date: 2023/5/12 17:56
* @version: 1.0
*/
public class ConfigerManager {
private static ConfigerManager driverManager;
private static Properties properties;
private ConfigerManager()
{
InputStream inputStream = null;
try {
properties = new Properties();
inputStream = ConfigerManager.class.getClassLoader().getResourceAsStream("jdbc.properties");
properties.load(inputStream);
} catch (Exception e) {
e.printStackTrace();
}finally{
if (inputStream != null)
try {
inputStream. close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static synchronized ConfigerManager getInstance()
{
if (driverManager == null) {
driverManager = new ConfigerManager();
}
return driverManager;
}
public String getValue(String key)
{
return properties. getProperty(key);
}
}
5.jdbc.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/test1?useSSL=false username=root password=root
6. userDao
public interface UserDao {
User getUsers(int user_id);
}
7. userDaoImpl
package com.it.www.daoImpl;
import com.it.www.dao.UserDao;
import com.it.www.pojo.User;
import com.it.www.util.BaseDao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @projectName: spring_demo01
* @package: com.it.www.daoImpl
* @className: userDaoImpl
* @author: Sun
* @description: TODO
* @date: 2023/5/12 17:00
* @version: 1.0
*/
public class UserDaoImpl extends BaseDao implements UserDao {
@Override
public User getUsers(int user_id) {
User user = new User();
try {
ResultSet rs = this. executeQuery("select * from user where user_id = ?", user_id);
if (rs. next()){
user = new User(
rs. getInt("user_id"),
rs. getString("user_name"),
rs. getString("user_password")
);
}
} catch (SQLException throwables) {
throwables. printStackTrace();
} finally {
this. closeResource();
}
return user;
}
}
8.Test class
The inversion of control of spring is used, so the userDaoImpl object is created through the spring container proxy.
@Test
public void getUserById(){
ApplicationContext context = new ClassPathXmlApplicationContext("spring.xml");
UserDao userDao = (UserDaoImpl)context.getBean("userDaoImpl");
User users = userDao. getUsers(1);
System.out.println(users);
}
2. DBCP
1. Use Maven to import dependencies
<dependency>
<groupId>commons-pool</groupId>
<artifactId>commons-pool</artifactId>
<version>1.5.4</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
2.DBCPUtil.java
package com.it.www.util;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class DBCPUtil {
private static DataSource dataSource = null;
private static Connection connection = null;
private static PreparedStatement preparedStatement = null;
private static ResultSet resultSet = null;
static {
try {
InputStream inputStream = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcp.properties");
Properties properties = new Properties();
properties.load(inputStream);
dataSource = BasicDataSourceFactory. createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
// Get it from the connection pool
try {
return dataSource. getConnection();
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
public static void close() {
if (connection != null) {
try {
connection. close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
connection = null;
}
if (preparedStatement != null) {
try {
preparedStatement. close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
preparedStatement = null;
}
if (resultSet != null) {
try {
resultSet. close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
resultSet = null;
}
}
public static int executeUpdate(String sql, Object... params) throws SQLException {
connection = DBCPUtil. getConnection();
int row = 0;
if(connection!= null){
preparedStatement = connection. prepareStatement(sql);
if (params!=null){
for (int i = 0; i < params. length; i ++ ) {
preparedStatement.setObject(i + 1,params[i]);
}
}
row = preparedStatement. executeUpdate();
}
return row;
}
public static ResultSet executeQuery(String sql, Object... params) throws SQLException {
resultSet = null;
connection = DBCPUtil. getConnection();
if (connection!=null){
PreparedStatement pstmt = connection. prepareStatement(sql);
if (params!=null & amp; & amp; params.length>0){
for (int i = 0; i<params. length; i ++ ){
pstmt.setObject(i + 1,params[i]);
}
}
resultSet = pstmt. executeQuery();
}
return resultSet;
}
}
3.dbcp.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/test1?useSSL=false username=root password=root
4. userDao
public interface UserDao {
User getUsers(int user_id);
}
5. userDaoImpl
public class UserDaoImpl implements UserDao {
@Override
public User getUsers(int user_id) {
User user = new User();
try {
ResultSet rs = DBCPUtil.executeQuery("select * from user where user_id = ?",user_id);
if (rs. next()){
user = new User(
rs. getInt("user_id"),
rs. getString("user_name"),
rs. getString("user_password")
);
}
} catch (SQLException throwables) {
throwables. printStackTrace();
} finally {
DBCPUtil. close();
}
return user;
}
}