Article directory
- JDBC database connection
-
- 1 guide package
- 2 Write configuration file
- 3. Write code to connect to the database
- 4 Test tools
- 5 additional
-
- 1 General CRUD class
- 2 Test CURD class
- 3 tests
JDBC database connection
This article takes the MySQL database as an example. If you want to switch to other databases, you only need to modify the jdbc.properties configuration file in the resource folder.
Project structure display:
1 Guide package
Import the MySQL driver package into the lib directory. After importing, right-click AS library to add the driver package to the project. Otherwise, a Class not found exception will be reported during runtime.
Summary of driver package download address: https://blog.csdn.net/weixin_43671437/article/details/134141851?spm=1001.2014.3001.5501
2 Write configuration file
Create the jdbc.properties configuration file in the Resource directory.
## MySQL 8.x user=root password=root url=jdbc:mysql://localhost:3306/jdbc_learn?useUnicode=true &characterEncoding=utf8 &rewriteBatchedStatements=true driverClass=com.mysql.cj.jdbc.Driver ## SQLServer connection properties #url=jdbc:sqlserver://localhost:1433;DatabaseName=SqlMatch2005_5;encrypt=true;trustServerCertificate=true #driverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver #user=sa #password=root
3 Write code to connect to the database
Create a JDBCUtil.java tool class in the java –> util package
/**
* ClassName: JDBCUtil
* Description: Tool class for operating database
*
* @Create 2023/10/29 14:35
* @Version 1.0
*/
public class JDBCUtil {<!-- -->
/*
Get database connection
*/
public static Connection getConnection() throws Exception {<!-- -->
// 1 Read 4 pieces of information in the configuration file
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
// 2 Load driver
Class.forName(driverClass);
// 3 Get the connection
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
/**
* Close resource operations, used when adding, deleting or modifying
* @param conn
* @param ps
*/
public static void closeResource(Connection conn, Statement ps){<!-- -->
// 7 Resource close
try {<!-- -->
if (ps != null)
ps.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
try {<!-- -->
if (conn != null)
conn.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
/**
* The operation of closing resources, used when querying
* @param conn
* @param ps
* @param rs
*/
public static void closeResource(Connection conn, Statement ps, ResultSet rs){<!-- -->
// 7 Resource close
try {<!-- -->
if (ps != null)
ps.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
try {<!-- -->
if (conn != null)
conn.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
try {<!-- -->
if (rs != null) {<!-- -->
rs.close();
}
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
}
4 Test tool class
If the test is successful, a connection can be returned.
@Test
public void testConnection6() throws Exception {<!-- -->
Connection conn = JDBCUtil.getConnection();
System.out.println(conn); // com.mysql.cj.jdbc.ConnectionImpl@1cbbffcd
JDBCUtil.closeResource(conn,null);
}
5 Extras
1 General CRUD class
Create the BaseDao.java class, which can directly inherit baseDao to implement addition, deletion, modification and query of the database.
/**
* ClassName: BaseDao
* Description: DAO: data(base) access object database access object
* Encapsulates the addition, deletion, modification and query operations for data tables
*
* @Create 2023/11/1 13:46
* @Version 1.0
*/
public abstract class BaseDao<T> {<!-- -->
private Class<T> clazz = null;
{<!-- -->
// Get the current BaseDao subclass to inherit the generic type of the parent class
Type genericSuperclass = this.getClass().getGenericSuperclass();
ParameterizedType paramType = (ParameterizedType)genericSuperclass; // Obtained the generic parameters of the parent class
Type[] typeArguments = paramType.getActualTypeArguments();
clazz = (Class<T>) typeArguments[0]; // The first parameter of the generic type
}
/**
* Query method for general tables, but only items of data can be queried
*
* @param conn
* @param sql
* @param args
* @return returns an object
*/
public T getInstance(Connection conn, String sql, Object... args) {<!-- -->
PreparedStatement ps = null;
ResultSet rs = null;
try {<!-- -->
ps = conn.prepareStatement(sql);
// Assign values to parameters
for (int i = 0; i < args.length; i + + ) {<!-- -->
ps.setObject(i + 1, args[0]);
}
// Get the result set
rs = ps.executeQuery();
// Get the metadata of the result set: ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
// Get the number of columns in the result set through ResultSetMetaData
int columnCount = rsmd.getColumnCount();
if (rs.next()) {<!-- -->
T t = clazz.newInstance();
// Process each column in the result set
for (int i = 0; i < columnCount; i + + ) {<!-- -->
// Get the column value of each column through ResultSet
Object columnValue = rs.getObject(i + 1);
// Get the column name of each column through ResultSetMetaData
// Get the column name getColumnName() -- deprecated
// Get the alias of the column getColumnLabel() returns the column name if there is no alias
String columnLabel = rsmd.getColumnLabel(i + 1);
// Specify the columnName attribute for the T object, assign it to columnValue, and use reflection
// In layman's terms: Match the attributes in customer with the column names and assign values to the attributes.
// Note: If the attribute is orderID and the column name is order_id, you need to alias the column name to make it consistent with the attribute name.
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue); // Assignment
}
return t;
}
} catch (Exception e) {<!-- -->
e.printStackTrace();
} finally {<!-- -->
// Resource close
JDBCUtil.closeResource(null, ps, rs);
}
return null;
}
/**
* Query operations on general tables can return multiple pieces of data.
*
* @param conn
* @param sql
* @param args
* @return returns a collection of objects
*/
public List<T> getForList(Connection conn, String sql, Object... args) {<!-- -->
PreparedStatement ps = null;
ResultSet rs = null;
try {<!-- -->
ps = conn.prepareStatement(sql);
// Assign values to parameters
for (int i = 0; i < args.length; i + + ) {<!-- -->
ps.setObject(i + 1, args[0]);
}
// Get the result set
rs = ps.executeQuery();
// Get the metadata of the result set: ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
// Get the number of columns in the result set through ResultSetMetaData
int columnCount = rsmd.getColumnCount();
//Create a collection object to store the results of the query
ArrayList<T> list = new ArrayList<>();
// Loop through each row of data
while (rs.next()) {<!-- -->
T t = clazz.newInstance();
// Process each column in the result set
for (int i = 0; i < columnCount; i + + ) {<!-- -->
// Get the column value of each column through ResultSet
Object columnValue = rs.getObject(i + 1);
// Get the column name of each column through ResultSetMetaData
// Get the column name getColumnName() -- deprecated
// Get the alias of the column getColumnLabel() returns the column name if there is no alias
String columnLabel = rsmd.getColumnLabel(i + 1);
// Specify the columnName attribute for the T object, assign it to columnValue, and use reflection
// In layman's terms: Match the attributes in customer with the column names and assign values to the attributes.
// Note: If the attribute is orderID and the column name is order_id, you need to alias the column name to make it consistent with the attribute name.
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue); // Assignment
}
list.add(t);
}
// Return the data collection
return list;
} catch (Exception e) {<!-- -->
e.printStackTrace();
} finally {<!-- -->
// Resource close
JDBCUtil.closeResource(null, ps, rs);
}
return null;
}
/**
* General addition, deletion and modification operations -- version 2.0 adds transactions
*
* @param conn
* @param sql
* @param args
* @return
*/
public int updateTable(Connection conn, String sql, Object... args) {<!-- --> // The number of placeholders in sql is the same as the number of variable parameters
PreparedStatement ps = null;
try {<!-- -->
// 2 Precompile the sql statement and return an instance of PreparedStatement
ps = conn.prepareStatement(sql);
// 3 fill in the placeholder
for (int i = 0; i < args.length; i + + ) {<!-- -->
ps.setObject(i + 1, args[i]); // Pay attention to the parameter error. In SQL, the array starts from 1 and the array starts from 0.
}
// 4 Execute sql statement
// boolean execute = ps.execute();
// if (!execute) System.out.println("Execution successful!!!");
//Return how many rows of data were affected
return ps.executeUpdate();
} catch (Exception e) {<!-- -->
e.printStackTrace();
} finally {<!-- -->
// Change to automatic submission after modification
// Mainly for the use of database connection pool
try {<!-- -->
conn.setAutoCommit(true);
} catch (SQLException e) {<!-- -->
throw new RuntimeException(e);
}
// 5 Resource close
JDBCUtil.closeResource(null, ps);
}
return 0;
}
/**
* General method for querying special values
* @param conn
* @param sql
* @param args
* @return
* @param <E>
*/
public <E> E getValue(Connection conn, String sql, Object... args) {<!-- -->
PreparedStatement ps = null;
ResultSet rs = null;
try {<!-- -->
ps = conn.prepareStatement(sql);
//fill placeholder
for (int i = 0; i < args.length; i + + ) {<!-- -->
ps.setObject(i + 1, args[i]);
}
// Get query results
rs = ps.executeQuery();
if (rs.next()) {<!-- -->
return (E) rs.getObject(1);
}
} catch (SQLException e) {<!-- -->
throw new RuntimeException(e);
} finally {<!-- -->
JDBCUtil.closeResource(null, ps, rs);
}
return null;
}
}
2 Test CURD class
Implement the addition, deletion, modification and query of the Customers class as well as the query of special values in the Dao layer
1 Create CustomersDao.java interface
/**
* ClassName: CustomersDao
* Description: This interface is used to standardize common operations on the Customers table
*
* @Create 2023/11/1 14:06
* @Version 1.0
*
*/
public interface CustomersDao {<!-- -->
/**
* Add the cust object to the database
* @param conn
* @param cust
*/
void insert(Connection conn, Customer cust);
/**
* Delete a record in the table for the specified id
* @param conn
* @param id
*/
void deleteById(Connection conn, int id);
/**
* For the cust object in memory, modify the specified record in the data table
* @param conn
* @param cust
*/
void update(Connection conn, Customer cust);
/**
* Query the Customer object of the object based on the specified ID
* @param conn
* @param id
* @return
*/
Customer getCustomerById(Connection conn,int id);
/**
* Return all Customer objects
* @param conn
* @return
*/
List<Customer> getAllCustomers(Connection conn);
/**
* Returns the data entry in the data table
* @param conn
* @return
*/
Long getCount(Connection conn);
/**
* Return the largest birthday in the table
* @param conn
* @return
*/
Date getMaxBirth(Connection conn);
}
2 Write the implementation class of CustomersDao interface
Create the CustomerDaoImpl.java file, inherit BaseDao and implement the CustomersDao interface.
/**
* ClassName: CustomerDaoImpl
* Description:
*
* @Create 2023/11/1 14:48
* @Version 1.0
*/
public class CustomerDaoImpl extends BaseDao<Customer> implements CustomersDao {<!-- -->
@Override
public void insert(Connection conn, Customer cust) {<!-- -->
String sql = "insert into customers(name,email,birth) values(?,?,?) ";
updateTable(conn, sql, cust.getName(), cust.getEmail(), cust.getBirth());
}
@Override
public void deleteById(Connection conn, int id) {<!-- -->
String sql = "delete from customers where id = ?";
updateTable(conn, sql, id);
}
@Override
public void update(Connection conn, Customer cust) {<!-- -->
String sql = "update customers set name = ?, email = ?, birth = ? where id = ?";
updateTable(conn, sql, cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
}
@Override
public Customer getCustomerById(Connection conn, int id) {<!-- -->
String sql = "select id, name,email,birth from customers where id = ?";
return getInstance(conn, sql, id);
}
@Override
public List<Customer> getAllCustomers(Connection conn) {<!-- -->
String sql = "select id, name,email,birth from customers";
return getForList(conn, sql);
}
@Override
public Long getCount(Connection conn) {<!-- -->
String sql = "select count(*) from customers";
return getValue(conn,sql);
}
@Override
public Date getMaxBirth(Connection conn) {<!-- -->
String sql = "select max(birth) from customers";
return getValue(conn,sql);
}
}
3 Test
Create test class CustomerDaoImplTest to test CustomerDaoImpl
/**
* ClassName: CustomerDaoImplTest
* Description:
*
* @Create 2023/11/1 15:18
* @Version 1.0
*/
public class CustomerDaoImplTest {<!-- -->
private CustomerDaoImpl dao = new CustomerDaoImpl();
@Test
public void insert() {<!-- -->
Connection conn = null;
try {<!-- -->
conn = JDBCUtil.getConnection();
Customer cust = new Customer(1,"songsong","[email protected]",new Date(21312425312L));
dao.insert(conn,cust);
} catch (Exception e) {<!-- -->
throw new RuntimeException(e);
} finally {<!-- -->
JDBCUtil.closeResource(conn,null);
}
}
@Test
public void deleteById() {<!-- -->
Connection conn = null;
try {<!-- -->
conn = JDBCUtil.getConnection();
dao.deleteById(conn,2);
} catch (Exception e) {<!-- -->
throw new RuntimeException(e);
} finally {<!-- -->
JDBCUtil.closeResource(conn,null);
}
}
@Test
public void update() {<!-- -->
Connection conn = null;
try {<!-- -->
conn = JDBCUtil.getConnection();
Customer cust = new Customer(1,"songsong","[email protected]",new Date(21312425312L));
dao.update(conn,cust);
} catch (Exception e) {<!-- -->
throw new RuntimeException(e);
} finally {<!-- -->
JDBCUtil.closeResource(conn,null);
}
}
@Test
public void getCustomerById() {<!-- -->
Connection conn = null;
try {<!-- -->
conn = JDBCUtil.getConnection();
System.out.println(dao.getCustomerById(conn, 4));
} catch (Exception e) {<!-- -->
throw new RuntimeException(e);
} finally {<!-- -->
JDBCUtil.closeResource(conn,null);
}
}
@Test
public void getAllCustomers() {<!-- -->
Connection conn = null;
try {<!-- -->
conn = JDBCUtil.getConnection();
List<Customer> customerList = dao.getAllCustomers(conn);
customerList.forEach(System.out::println);
} catch (Exception e) {<!-- -->
throw new RuntimeException(e);
} finally {<!-- -->
JDBCUtil.closeResource(conn,null);
}
}
@Test
public void getCount() {<!-- -->
Connection conn = null;
try {<!-- -->
conn = JDBCUtil.getConnection();
System.out.println(dao.getCount(conn));
} catch (Exception e) {<!-- -->
throw new RuntimeException(e);
} finally {<!-- -->
JDBCUtil.closeResource(conn,null);
}
}
@Test
public void getMaxBirth() {<!-- -->
Connection conn = null;
try {<!-- -->
conn = JDBCUtil.getConnection();
System.out.println(dao.getMaxBirth(conn));
} catch (Exception e) {<!-- -->
throw new RuntimeException(e);
} finally {<!-- -->
JDBCUtil.closeResource(conn,null);
}
}
}