69期-Java SE-041_JDBC-5 - HiJackykun

DBUtlis

```java
package com.southwind.mydbutils;

import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MyQueryRunner {

    public Object query(Connection connection, String sql, ResultSetHandler rsh,Object... params){
        Object result = null;
        if(connection == null){
            throw new NullPointerException("Connection is null");
        }else if(sql == null){
            throw new NullPointerException("sql is null");
        }else if(rsh == null){
            throw new NullPointerException("rsh is null");
        }else{
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;

            try {
                preparedStatement = connection.prepareStatement(sql);
                if(params.length>0){
                    for(int i = 0;i < params.length;i++){
                        Object param = params[i];
                        String typeName = param.getClass().getTypeName();
                        switch (typeName){
                            case "java.lang.Integer":
                                Integer integer = (Integer) param;
                                preparedStatement.setInt(i+1,integer);
                                break;
                            case "java.lang.String":
                                String string = (String) param;
                                preparedStatement.setString(i+1,string);
                                break;
                        }
                    }
                }
                resultSet = preparedStatement.executeQuery();
                result = rsh.handle(resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if(preparedStatement != null){
                        preparedStatement.close();
                    }
                    if(resultSet != null){
                        resultSet.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return result;
    }
}
```



```java
package com.southwind.mydbutils;

import com.southwind.entity.People;
import org.apache.commons.dbutils.ResultSetHandler;

import java.lang.reflect.Constructor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class MyBeanHandler implements ResultSetHandler {

    private Class clazz;

    public MyBeanHandler(Class clazz){
        this.clazz = clazz;
    }

    @Override
    public Object handle(ResultSet resultSet) throws SQLException {
        Object result = null;
        try {
            Constructor constructor = this.clazz.getConstructor(null);
            result = constructor.newInstance();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int count = metaData.getColumnCount();
            if(resultSet.next()){
                for(int i = 1;i<=count;i++){
                    String columnName = metaData.getColumnName(i);
                    String columnType = metaData.getColumnTypeName(i);
                    Object value = null;
                    Class propertyType = null;
                    switch (columnType){
                        case "INT":
                            value = resultSet.getInt(i);
                            propertyType = Integer.class;
                            break;
                        case "VARCHAR":
                            value = resultSet.getString(i);
                            propertyType = String.class;
                            break;
                    }
                    String methodName = "set"+columnName.substring(0,1).toUpperCase()+columnName.substring(1);
                    Method method = this.clazz.getMethod(methodName,propertyType);
                    method.invoke(result,value);
                }
            }
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return result;
    }
}
```



```java
package com.southwind.mydbutils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.southwind.entity.User;

import java.sql.Connection;
import java.sql.SQLException;

public class Test {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            ComboPooledDataSource dataSource = new ComboPooledDataSource("testc3p0");
            connection = dataSource.getConnection();
            String sql = "select * from t_user where id = ?";
            MyQueryRunner myQueryRunner = new MyQueryRunner();
//            People people = (People) myQueryRunner.query(connection,sql,new MyBeanHandler(People.class));
//            System.out.println(people);
            User user = (User) myQueryRunner.query(connection,sql,new MyBeanHandler(User.class),2);
            System.out.println(user);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
```



Java Web：Servlet、JSP、Filter、Listener

将 Java Application 部署到 Web 容器后，启动 Web 容器，客户端就可以通过网络请求来调用 Java 相关方法。

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>

    <named-config name="testc3p0">
        
        <!-- 指定连接数据源的基本属性 -->
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=UTF-8</property>
        
        <!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化数据库连接池时连接的数量 -->
        <property name="initialPoolSize">5</property>
        <!-- 数据库连接池中的最小的数据库连接数 -->
        <property name="minPoolSize">5</property>
        <!-- 数据库连接池中的最大的数据库连接数 -->
        <property name="maxPoolSize">10</property>
    
    </named-config>
        
</c3p0-config>

People.java

package com.southwind.entity;

public class People {
    private Integer id;
    private String name;
    private Integer money;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getMoney() {
        return money;
    }

    public void setMoney(Integer money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "People{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

User.java

package com.southwind.entity;

public class User {
    private Integer id;
    private String username;
    private String password;
    private Integer age;
    private String img;
    private String realname;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getImg() {
        return img;
    }

    public void setImg(String img) {
        this.img = img;
    }

    public String getRealname() {
        return realname;
    }

    public void setRealname(String realname) {
        this.realname = realname;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", age=" + age +
                ", img='" + img + '\'' +
                ", realname='" + realname + '\'' +
                '}';
    }
}

MyBeanHandler.java

package com.southwind.mydbutils;

import com.southwind.entity.People;
import org.apache.commons.dbutils.ResultSetHandler;

import java.lang.reflect.Constructor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class MyBeanHandler implements ResultSetHandler {

    private Class clazz;

    public MyBeanHandler(Class clazz){
        this.clazz = clazz;
    }

    @Override
    public Object handle(ResultSet resultSet) throws SQLException {
        Object result = null;
        try {
            Constructor constructor = this.clazz.getConstructor(null);
            result = constructor.newInstance();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int count = metaData.getColumnCount();
            if(resultSet.next()){
                for(int i = 1;i<=count;i++){
                    String columnName = metaData.getColumnName(i);
                    String columnType = metaData.getColumnTypeName(i);
                    Object value = null;
                    Class propertyType = null;
                    switch (columnType){
                        case "INT":
                            value = resultSet.getInt(i);
                            propertyType = Integer.class;
                            break;
                        case "VARCHAR":
                            value = resultSet.getString(i);
                            propertyType = String.class;
                            break;
                    }
                    String methodName = "set"+columnName.substring(0,1).toUpperCase()+columnName.substring(1);
                    Method method = this.clazz.getMethod(methodName,propertyType);
                    method.invoke(result,value);
                }
            }
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return result;
    }
}

MyQueryRunner.java

package com.southwind.mydbutils;

import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MyQueryRunner {

    public Object query(Connection connection, String sql, ResultSetHandler rsh,Object... params){
        Object result = null;
        if(connection == null){
            throw new NullPointerException("Connection is null");
        }else if(sql == null){
            throw new NullPointerException("sql is null");
        }else if(rsh == null){
            throw new NullPointerException("rsh is null");
        }else{
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;

            try {
                preparedStatement = connection.prepareStatement(sql);
                if(params.length>0){
                    for(int i = 0;i < params.length;i++){
                        Object param = params[i];
                        String typeName = param.getClass().getTypeName();
                        switch (typeName){
                            case "java.lang.Integer":
                                Integer integer = (Integer) param;
                                preparedStatement.setInt(i+1,integer);
                                break;
                            case "java.lang.String":
                                String string = (String) param;
                                preparedStatement.setString(i+1,string);
                                break;
                        }
                    }
                }
                resultSet = preparedStatement.executeQuery();
                result = rsh.handle(resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if(preparedStatement != null){
                        preparedStatement.close();
                    }
                    if(resultSet != null){
                        resultSet.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return result;
    }
}

Test.java

package com.southwind.mydbutils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.southwind.entity.User;

import java.sql.Connection;
import java.sql.SQLException;

public class Test {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            ComboPooledDataSource dataSource = new ComboPooledDataSource("testc3p0");
            connection = dataSource.getConnection();
            String sql = "select * from t_user where id = ?";
            MyQueryRunner myQueryRunner = new MyQueryRunner();
//            People people = (People) myQueryRunner.query(connection,sql,new MyBeanHandler(People.class));
//            System.out.println(people);
            User user = (User) myQueryRunner.query(connection,sql,new MyBeanHandler(User.class),2);
            System.out.println(user);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Test.java

package com.southwind.test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.southwind.entity.People;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.MapHandler;

import java.sql.Connection;
import java.sql.SQLException;

public class Test {
    public static void main(String[] args) {
        try {
            ComboPooledDataSource dataSource = new ComboPooledDataSource("testc3p0");
            Connection connection = dataSource.getConnection();
            String sql = "select * from people where id = 1";
            QueryRunner queryRunner = new QueryRunner();
            People people = queryRunner.query(connection,sql,new BeanHandler<>(People.class));
            System.out.println(people);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

        }
    }
}

posted on 2019-07-13 23:23 HiJackykun 阅读(317) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部