package com.ftj.DS.practice;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * Druid工具类
 */
public class ComUtils {

    private static DataSource ds;

    //静态代码块加载配置文件
    static {
        try {
            Properties pro = new Properties();
            InputStream is = ComUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            pro.load(is);
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取数据库连接
    public static Connection getConn() throws SQLException {
        return ds.getConnection();
    }

    //获取连接
    public static DataSource getDs() {
        return ds;
    }

    //关闭数据库资源
    public static void close(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                //归还连接
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //关闭数据库连接
    public static void close(Connection conn, PreparedStatement pstmt) {
        close(conn, pstmt, null);
    }
}

package com.ftj.DS.practice;

import com.ftj.DS.entity.User;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 练习
 */
public class Work01 {

    //创建DJBCTemplate对象，依赖于数据源DataSource
    private JdbcTemplate template = new JdbcTemplate(ComUtils.getDs());

    //修改user表的密码为 654321
    @Test
    public void test01() {
        //调用创建DJBCTemplate对象下的方法来执行sql
        String sql = "update user set password = ? where id = ?";
        int update = template.update(sql, "abc", 1);
        if (update > 0) {
            System.out.println("密码更新成功！");
        }
    }

    //添加一条记录
    @Test
    public void test02() {
        //调用创建DJBCTemplate对象下的方法来执行sql
        String sql = "insert into user values(null,?,?)";
        int admin = template.update(sql, "admin", "123");
        if (admin > 0){
            System.out.println("success");
        }
    }

    //查询id为1的记录，封装为Map集合  注意：这个方法查询的结果集长度只能是1
    @Test
    public void test03(){
        String sql = "select * from user where id = 1";
        Map<String, Object> map = template.queryForMap(sql);
        System.out.println(map);
    }

    /*
    查询所有记录将其封装为List
     */
    @Test
    public void test04(){
        String sql = "select * from user";
        List<Map<String, Object>> maps = template.queryForList(sql);
        for (Map<String, Object> map : maps) {
            System.out.println(map);
        }
    }


    //查询所有记录，将其封装为User对象的List集合
    @Test
    public void test06(){
        String sql = "select * from user";
        List<User> lsit = template.query(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int i) throws SQLException {
                User user = new User();
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                user.setId(id);
                user.setUsername(username);
                user.setPassword(password);
                return user;
            }
        });
        for (User user : lsit) {
            System.out.println(user);
        }
    }

    //优化
    @Test
    public void test06_2(){
        String sql = "select * from user";
        List<User> list = template.query(sql, new BeanPropertyRowMapper<User>(User.class));
        for (User user : list) {
            System.out.println(user);
        }
    }

    //查询所有记录
    @Test
    public void test07(){
        String sql = "select count(id) from user";
        Long total = template.queryForObject(sql, Long.class);
        System.out.println(total);
    }
}