自定义MyJdbcTemplate

一.JavaBean类

public class User {
    private Integer id;
    private String username;
    private String password;
    private String nickname;
    @Override
    public String toString() {
        return "User{" +
        "id=" + id +
        ", username='" + username + '\'' +
        ", password='" + password + '\'' +
        ", nickname='" + nickname + '\'' +
                '}';
    }

    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 String getNickname() {
        return nickname;
    }

    public void setNickname(String nickname) {
        this.nickname = nickname;
    }
}

二.工具类JDBCUtil

public class JDBCUtil {
    private static DataSource dataSource;
    static {
        //1.创建Druid连接池对象
        //读取druidconfig.properties配置文件
        ClassLoader classLoader = JDBCUtil.class.getClassLoader();
        InputStream is = classLoader.getResourceAsStream("druidconfig.properties");
        Properties properties = new Properties();
        try {
            properties.load(is);
            //使用工厂类创建
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接池对象的方法
     * @return
     */
    public static DataSource getDataSource(){
        return dataSource;
    }
}

三.自定义MyJdbcTemplate类

public class MyJdbcTemplate {
    private DataSource dataSource;

    public MyJdbcTemplate(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * 执行增删改的SQL语句的方法
     * @param sql
     * @param params
     * @return
     */
    public int update(String sql,Object ... params) {
        //1.获取连接对象
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            conn = dataSource.getConnection();
            //2.预编译SQL语句
            pstm = conn.prepareStatement(sql);
            //3.设置SQL语句的参数
            //通过参数元数据获取参数的个数
            ParameterMetaData metaData = pstm.getParameterMetaData();
            int parameterCount = metaData.getParameterCount();
            for (int i = 1; i <= parameterCount; i++) {
                //设置每一个参数
                pstm.setObject(i, params[i - 1]);
            }
            //4.执行SQL语句
            int i = pstm.executeUpdate();
            return i;
        } catch (SQLException e) {
            throw new RuntimeException("执行失败!!!");
        } finally {
            try {
                pstm.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 查询一行数据，并且将数据封装到JavaBean对象中
     * @param sql
     * @param clazz
     * @param params
     * @param <T>
     * @return
     */
    public <T> T queryForObject(String sql,Class<T> clazz,Object ... params) {
        //1.获得连接
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rst = null;
        try {
            conn = dataSource.getConnection();
            //2.预编译SQL语句
            pstm = conn.prepareStatement(sql);
            //3.设置SQL语句的参数
            ParameterMetaData metaData = pstm.getParameterMetaData();
            int parameterCount = metaData.getParameterCount();
            for (int i = 1; i <= parameterCount; i++) {
                //设置每一个参数
                pstm.setObject(i, params[i - 1]);
            }
            //4.执行SQL语句
            rst = pstm.executeQuery();

            //获取结果集元数据
            ResultSetMetaData rstMetaData = rst.getMetaData();
            int columnCount = rstMetaData.getColumnCount();//总列数

            //5.遍历结果集，将结果集中的数据封装到JavaBean对象中
            T t = clazz.newInstance();//这就是那个JavaBean对象

            //获取该JavaBean类的所有公有方法
            Method[] methods = clazz.getMethods();

            while (rst.next()) {
                //获取结果集中的每一个字段的值
                //遍历获取每一列的列名
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = rstMetaData.getColumnName(i);//这就是每一列的列名
                    //获取每一列的值
                    Object value = rst.getObject(columnName);

                    //将每列的值设置到JavaBean对象中
                    //也就是调用这个对象的set方法进行设置
                    //遍历出每一个公有方法
                    for (Method method : methods) {
                        //对比方法名,id ---- > setId()
                        String methodName = method.getName();
                        if (methodName.equalsIgnoreCase("set" + columnName)) {
                            //就调用这个方法设置值
                            method.invoke(t, value);
                        }
                    }
                }
            }
            return t;
        } catch (Exception e) {
            throw new RuntimeException("查询失败");
        } finally {
            try {
                rst.close();
                pstm.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

四.测试类

public class TestMyJdbcTemplate {
    @Test
    public void testUpdate(){
        MyJdbcTemplate myJdbcTemplate = new MyJdbcTemplate(JDBCUtil.getDataSource());

        String sql = "update user set password=? where id=?";

        int update = myJdbcTemplate.update(sql, "1234567", "1");
        System.out.println(update);
    }

    @Test
    public void testQueryForObject(){
        MyJdbcTemplate myJdbcTemplate = new MyJdbcTemplate(JDBCUtil.getDataSource());
        String sql = "select * from user where id=?";
        User user = myJdbcTemplate.queryForObject(sql, User.class, 1);

        System.out.println(user);
    }
}