Recently, I have been reviewing some things in jdbc. I feel that after using the spring family bucket, I only know how to use its encapsulated annotations, but I forgot some basic things. It seems that I have become a spring programmer instead of a java programmer. However, I am reviewing During jdbc, I found that some frequently repeated operations could not be omitted, so I thought of a way to simplify it. I know that the tools such as JdbcTemplate and Mybatis currently used on the market can solve these problems very well, but I just want to write one myself Simple tools to solve this hassle with reflection and generics. Of course, mybatis and mp are still used in actual development. This solution is just a small practice. Try to write a simple JdbcTemplate by hand.
Let's first take a look at the operation of native jdbc:
public class TestJDBC {
Connection connection;
@Before
public void before(){
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql:///db_test","root", "root");
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void testJdbc() throws SQLException {
PreparedStatement ps = connection.prepareStatement("select * from t_user where id = ?");
ps.setString(1,"1");
//... how many times how many placeholders are set
//dql uses executeQuery, dml uses execute
ResultSet rs = ps.executeQuery();//ps.execute
User user = new User();
while (rs.next()){
user.setId(rs.getLong("id"));
user.setUsername(rs.getString("username"));
//...how many fields need to be set how many times, and similarly how many times rs need to get
}
//return user;
}
}There will be a lot of ps.setxxx methods and repeated code for operating rs when dql is executed, and when I want to get another entity class, I have to rewrite a method to set attributes and rs.get attributes, so I wrote a Something similar to JdbcTemplate can be reused when querying a single piece of data and multiple pieces of data.
The first is to write a method that can obtain all member variables of the class through reflection, find its set attribute, and then use the member variable name to find the corresponding field in the result set and attach the value to the entity class. Of course, in this case, the database field name must be followed by The member variable names of the entity class are the same, otherwise the assignment will fail.
Here's a little bit about the way to get the set attribute. I personally feel that there are other better methods. After all, getDeclaredMethods will get equals, toString and other methods together, which slows down the efficiency. But didn't go into it.
private <T> T autoInject(Class<T> clazz,ResultSet rs,Object instance) throws SQLException, InvocationTargetException, IllegalAccessException {
//Reflection to get all declared member variables
Field[] declaredFields = clazz.getDeclaredFields();
//Reflection gets all declared get and set methods
Method[] declaredMethods = clazz.getDeclaredMethods();
for (Field field : declaredFields) {
String fieldName = field.getName();
Class<?> type = field.getType();
Date date = null;
Object object= null;
//Call the getDate method when getting the date, otherwise it will fail to force the Date later, and I don't know why
if (type == Date.class){
date = rs.getDate(fieldName);
}else {
//In other cases, just call the getObject method
object = rs.getObject(fieldName);
}
for (Method method : declaredMethods) {
String methodNameWithoutGS = method.getName().substring(3);
String methodNameOnlyGS = method.getName().substring(0, 3);
//If the name of the method name after removing the three letters of get and set is the same as the member variable name, call the set method to assign a value to the generic class, and exclude the get method
if (methodNameWithoutGS.equalsIgnoreCase(fieldName) && !methodNameOnlyGS.equalsIgnoreCase("get")){
if (type == Date.class){
method.invoke(instance,date);
break;
}
method.invoke(instance,object);
break;
}
}
}
return (T) instance;
}Through this method, you can assign values to all attributes in the entity class. Of course, the field name in the rs (result set) must be the same as the member variable name.
Next, we need to solve this problem of ps.setxxx. Here we assign it a value through a for loop.
private PreparedStatement getPS(String sql,Object ...params) throws SQLException {
connection = dataSource.getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
//fill placeholders
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1,params[i]);
}
return ps;
}Finally, put him in the query method. Only dql is written here. Since there is no result set operation, dml will be much more convenient.
A general method for querying a piece of data:
public <T> T queryOne(String sql,Class<T> clazz,Object ...params) throws Exception{
PreparedStatement ps = getPS(sql, params);
ResultSet rs = ps.executeQuery();
try {
//Because it is a query for one result, the returned object is created outside. If there are multiple result sets, only the last result is returned.
T emptyInstance = clazz.getDeclaredConstructor().newInstance();
while (rs.next()){
T injectedInstance = autoInject(clazz, rs, emptyInstance);
return injectedInstance;
}
} finally {
closeResource(rs,ps);
}
return null;//no results
}Query with multiple results:
public <T> List<T> queryLots(String sql,Class<T> clazz,Object ...params) throws Exception{
List<T> list = new ArrayList<>();
PreparedStatement ps = getPS(sql, params);
ResultSet rs = ps.executeQuery();
try {
while (rs.next()){
T emptyInstance = clazz.getDeclaredConstructor().newInstance();
T injectedInstance = autoInject(clazz, rs, emptyInstance);
list.add(injectedInstance);
}
return list;
} finally {
closeResource(rs,ps);
}
}Although compared with JdbcTemplate, these functions still have many imperfections or bug s, but it feels good to solve the repeated operations that will occur when using native jdbc through reflection and generics, although it is of no practical use. . .
The complete code of the entire class is as follows:
/**
* @Author:Joker
* @Date:2022/5/10 12:10
* @Description:The parent class of all DAO s with available database connections and connection pools
*/
public abstract class BaseDao {
protected static Connection connection;
protected static DataSource dataSource;
static {
try (
InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("druid.properties");
){
Properties prop = new Properties();
prop.load(is);
dataSource = DruidDataSourceFactory.createDataSource(prop);
} catch (Exception e) {
e.printStackTrace();
}
}
public <T> T queryOne(String sql,Class<T> clazz,Object ...params) throws Exception{
PreparedStatement ps = getPS(sql, params);
ResultSet rs = ps.executeQuery();
try {
//Because it is a query for one result, the returned object is created outside. If there are multiple result sets, only the last result is returned.
T emptyInstance = clazz.getDeclaredConstructor().newInstance();
while (rs.next()){
T injectedInstance = autoInject(clazz, rs, emptyInstance);
return injectedInstance;
}
} finally {
closeResource(rs,ps);
}
return null;//no results
}
public <T> List<T> queryLots(String sql,Class<T> clazz,Object ...params) throws Exception{
List<T> list = new ArrayList<>();
PreparedStatement ps = getPS(sql, params);
ResultSet rs = ps.executeQuery();
try {
while (rs.next()){
T emptyInstance = clazz.getDeclaredConstructor().newInstance();
T injectedInstance = autoInject(clazz, rs, emptyInstance);
list.add(injectedInstance);
}
return list;
} finally {
closeResource(rs,ps);
}
}
private PreparedStatement getPS(String sql,Object ...params) throws SQLException {
connection = dataSource.getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
//fill placeholders
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1,params[i]);
}
return ps;
}
private <T> T autoInject(Class<T> clazz,ResultSet rs,Object instance) throws SQLException, InvocationTargetException, IllegalAccessException {
//Reflection to get all declared member variables
Field[] declaredFields = clazz.getDeclaredFields();
//Reflection gets all declared get and set methods
Method[] declaredMethods = clazz.getDeclaredMethods();
for (Field field : declaredFields) {
String fieldName = field.getName();
Class<?> type = field.getType();
Date date = null;
Object object= null;
//Call the getDate method when getting the date, otherwise it will fail to force the Date later, and I don't know why
if (type == Date.class){
date = rs.getDate(fieldName);
}else {
//In other cases, just call the getObject method
object = rs.getObject(fieldName);
}
for (Method method : declaredMethods) {
String methodNameWithoutGS = method.getName().substring(3);
String methodNameOnlyGS = method.getName().substring(0, 3);
//If the name of the method name after removing the three letters of get and set is the same as the member variable name, call the set method to assign a value to the generic class, and exclude the get method
if (methodNameWithoutGS.equalsIgnoreCase(fieldName) && !methodNameOnlyGS.equalsIgnoreCase("get")){
if (type == Date.class){
method.invoke(instance,date);
break;
}
method.invoke(instance,object);
break;
}
}
}
return (T) instance;
}
public void closeResource(ResultSet rs,PreparedStatement ps){
try {
if (connection != null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void closeResource(PreparedStatement ps){
closeResource(null,ps);
}
}