本周学习内容

• java语言基础、面向对象、集合类、本地文件操作、IO操作、多线程、网络通信和JDBC
• Maven的配置与使用
• Docker中的MySQL、Navicat和IDEA的连接与使用

JDBC

编写流程

• 加载驱动
• 打开连接
• 执行查询
• 处理结果
• 清理环境

mysql 增删改查的模板

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>

public class JDBCDemo {
    public static void main(String [] args){
        String sql = "SELECT * FROM tbl_user";
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = DriverManager.getConnection("jdbc:mysql://192.168.99.100:3306/aaoo","root","1234567");
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            while(rs.next()){
                System.out.print(rs.getInt("id")+" ");
                System.out.print(rs.getString("name")+" ");
                System.out.print(rs.getString("password")+" ");
                System.out.print(rs.getString("email")+ " ");
                System.out.println("");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

事务

• 原子性
• 一致性
• 隔离性

• 持久性

• 开始事务：BEGIN TRANSACTION

• 提交事务：COMMIT TRANSACTION

• 回滚事务：ROLLBACK TRANSACTION

优化

dbconfig.properties 要放在Resources目录中，并把Resources目录设置为Make Directory as—Resourses Root

• dbconfig.properties：

dburl=jdbc\:mysql\://192.168.99.100\:3306/aaoo
user=root
password=1234567

• 获取连接

public class ConnectionFactory {
    private static String dburl;
    private static String user;
    private static String password;

    private static ConnectionFactory factory = new ConnectionFactory();
    private Connection conn;
    static{
        // 保存属性文件中的键值对
        Properties properties = new Properties();
        try{
            // 首先获取当前类的类加载器，调用getResourceAsStream方法读取内容
            InputStream in = ConnectionFactory.class.getClassLoader().getResourceAsStream("dbconfig.properties");
            // 从输入流中读取值
            properties.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
        dburl = properties.getProperty("dburl");
        user = properties.getProperty("user");
        password = properties.getProperty("password");
    }
    private ConnectionFactory(){ }
    protected static ConnectionFactory getInstance(){
        return factory;
    }
    public Connection makeConnection(){
        try{
            conn = DriverManager.getConnection(dburl, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
}

• ID抽象类

public abstract class IdEntity {
    protected Long id;
    public Long getId(){
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }   
}

• User类

public class User extends IdEntity{
    private String name;
    private String password;
    private String email;
    public String getName() {
        return name;
    }
    public String getPassword() {
        return password;
    }
    public String getEmail() {
        return email;
    }
    public void setName(String name) {
        this.name = name;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    @Override
    public String toString() {
        return "Usr [name="+name+",password="+password+",email="+email+",id="+id+"]";
    }
}

• User建立连接的接口

DAO 提供了应用程序与数据库之间的操作规范和操作用于通常数据库的增删查改 一般如果使用框架 都是由框架自动生成，提高访问效率和便于快速开发。

public interface UserDao {
    public void save(Connection conn, User user)throws SQLException;
    public void update(Connection conn, Long id, User user)throws SQLException;
    public void delete(Connection conn, User user) throws SQLException;
}

• 增删改保存的实现 以IMPL 结尾的类一般是实现了某个或多个接口的类，这些类的接口是定义了一些规范的类，通常是数据访问等等，在service 中会通过spring的注入注入这些接口来实现逻辑。

public class UserDaoImpl implements UserDao{
    /**
     * 保存用户信息
     * @param conn
     * @param user
     * @throws SQLException
     */
    @Override
    public void save(Connection conn, User user) throws SQLException {
        PreparedStatement ps = conn.prepareCall("INSERT INTO tbl_user(name,password,email) VALUES (?,?,?)");
        ps.setString(1,user.getName());
        ps.setString(2,user.getPassword());
        ps.setString(3,user.getEmail());
        ps.execute();
    }
    // 更新用户信息
    @Override
    public void update(Connection conn, Long id, User user) throws SQLException {
        String updateSql = "UPDATE tbl_user SET name = ?, password = ?, email = ? WHERE id = ?";
        PreparedStatement ps = conn.prepareStatement(updateSql);
        ps.setString(1,user.getName());
        ps.setString(2,user.getPassword());
        ps.setString(3,user.getEmail());
        ps.setLong(4,id);
    }
    //删除用户信息
    @Override
    public void delete(Connection conn, User user) throws SQLException {
        String updateSql = "DELETE from tbl_user WHERE id = ?";
        PreparedStatement ps = conn.prepareStatement(updateSql);
        ps.setLong(1,user.getId());
        ps.execute();
    }
}

• 测试类

public class UserDaoTest {
    public static void main (String [] args){
        Connection conn = null;
        try{
            conn = ConnectionFactory.getInstance().makeConnection();
            conn.setAutoCommit(false);
            UserDao userDao = new UserDaoImpl();
            User tom = new User();
            tom.setName("Tom");
            tom.setPassword("123");
            tom.setEmail("tom@qq.com");
            userDao.save(conn, tom);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }
}