(JDBC使用及封装)实习第二天——之JDBC增删改查

最新推荐文章于 2024-04-30 23:00:09 发布

csdn199771

最新推荐文章于 2024-04-30 23:00:09 发布

阅读量271

点赞数

本文链接：https://blog.csdn.net/qq_39123056/article/details/95480823

版权

今天实习主要学习了通过SQLyog - 64 bit客户端连接管理数据库，在java项目中通过jdbc操纵数据库，并写出规范风格的代码。学习内容如下：

准备阶段

1.安装并打开SQLyog数据库可视化管理工具：

2.创建user数据库，字符集选择utf8mb4（兼容utf8），排序选择utf8mb4_general_ci：

其中关于排序规则的选择需要明确的是，mysql是根据所给的规则对数据库中的字符串进行相互比较来确定顺序，并排序的。意味其也可确定字符串是否相等或不等。网上查阅资料如下：

后缀ci (case insensitive)意味不区分大小写(大小写不敏感)，后缀cs (case sensitive)区分大小写(大小写敏感)
utf8_bin 规定每个字符串用二进制编码存储，区分大小写，可以直接存储二进制的内容
如ci情况下：select name,age from userinfo; 等价于SELECT NAME,AgE FROM userinfo; 大小写字符判断是一样的
而在cs情况下:假设字段名严格为name, age，表名:UserInfo。那么就必须:select name,age from UserInfo; 大小写字符判断有区分
而bin意思是二进制，所以小写u和大写U会被区别
例如你运行:
SELECT name FROM UserInfo WHERE name = 'Lina'
那么在utf8_bin中你就找不到 name = 'lina' 的那一行, 在utf8_general_ci 下可以.

1）. utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。

2）. utf8_general_cs 区分大小写，如果用户名和邮箱用这个就会照成不良后果

3）. utf8_bin:字符串每个字符串用二进制数据编译存储。区分大小写，而且可以存二进制的内容

3. 建userinfo表

其中将id设为主键，并勾选自增选项，这样在插入数据时数据库可以自动增加id值，避免重复。

4.插入一行数据并保存

一、数据库连接

public class Test {
    public static void main(String[] args) {
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.创建连接
            DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test_user?useSSL=true&characterEncoding=utf-8&user=root&password=password");
            System.out.println("创建连接成功");
            //3.写sql语句
            //4.得到statement对象执行sql
            //5.得到结果集
            //6.处理结果集
            //7.关闭资源
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

运行结果：创建连接成功

二、数据库操作基本框架

public class Test {
    private static void prtRS(ResultSet rs) throws SQLException {
        while (rs.next()) {
            System.out.print(rs.getInt(1) + "\t ");
            System.out.print(rs.getString(2) + '\t');
            System.out.print(rs.getString(3) + "\r\n");
        }
    }

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.创建连接
            connection =
                    DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test_user?useSSL=true&characterEncoding=utf-8&user=root&password=password");
            System.out.println("创建连接成功");
            //3.写sql语句
            String sql = "select * from userinfo";
            //4.得到statement对象执行sql
            statement = connection.prepareStatement(sql);
            //5.得到结果集
            rs = statement.executeQuery();
            //6.处理结果集
            prtRS(rs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //7.关闭资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}

三、封装数据库连接和关闭资源操作

public class DBUtil {
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test_user?useSSL=true&characterEncoding=utf-8&user=root&password=password");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void close(Connection con, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Connection conn, Statement stmt) {
        DBUtil.close(conn, stmt, null);
    }

}

使用封装好的DBUtil类建立和关闭数据库连接：

    public boolean add(UserInfo userInfo) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DBUtil.getConnection();
            //sql
            String sql = "insert into userinfo(username,password) values(?,?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, userInfo.getUsername());
            stmt.setString(2, userInfo.getPassword());
            //返回插入结果
            return stmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt);
        }
        return false;
    }

四、实现增删改查

五、建立实体类与数据库表对应

数据库表：

实体类：

package com.zhongruan.bean;

public class UserInfo {

    private int id;
    private String username;
    private String password;

    public int getId() {
        return id;
    }

    public UserInfo() {
    }

    public UserInfo(String username, String password) {
        this.username = username;
        this.password = password;
    }

    public UserInfo setId(int id) {
        this.id = id;
        return this;
    }

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

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

    @Override
    public String toString() {
        return String.format("UserInfo{id:%d, username:'%s', password:'%s'}", id, username, password);
    }

}

PS： 1.其中getter和setter方法在IDEA中用alt+insert键选择generate自动生成。

2.直接print实体对象只会显示其内存引用地址，只有重写Object的toString()方法可以使得输出能显示其属性及其值。

六、抽象出DAO层

1.在项目中新建一个名为dao的包，路径为：com.zhongruan.dao

2.在dao包中添加UserInfoDao类，该类中所有操作均为针对数据库表userinfo的操作:

public class UserInfoDao {
    public boolean add(UserInfo userInfo) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DBUtil.getConnection();
            //sql
            String sql = "insert into userinfo(username,password) values(?,?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, userInfo.getUsername());
            stmt.setString(2, userInfo.getPassword());
            //返回插入结果
            return stmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt);
        }
        return false;
    }

    public List<UserInfo> findAll() {
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        List<UserInfo> list = new ArrayList<>();
        try {
            //1.加载驱动
            //2.创建连接
            conn = DBUtil.getConnection();
            //3.写sql语句
            String sql = "select * from userinfo";
            //4.得到statement对象执行sql
            statement = conn.prepareStatement(sql);
            //5.得到结果集
            rs = statement.executeQuery();
            //6.处理结果集
            while (rs.next()) {
                UserInfo userInfo = new UserInfo();
                userInfo.setId(rs.getInt(1));
                userInfo.setUsername(rs.getString(2));
                userInfo.setPassword(rs.getString(3));
                list.add(userInfo);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //7.关闭资源
            DBUtil.close(conn, statement, rs);
        }
        return list;
    }

}

七、完整代码

1.项目结构：

2. 在测试类Test中调用封装好的DBUtil类、UserInfo、UserInfoDao类，查询数据库，插入一条信息，然后再查询更新后的数据库，并将其打印出来：

封装的dao层UserInfoDao类：

public class UserInfoDao {
    public boolean add(UserInfo userInfo) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DBUtil.getConnection();
            //sql
            String sql = "insert into userinfo(username,password) values(?,?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, userInfo.getUsername());
            stmt.setString(2, userInfo.getPassword());
            //返回插入结果
            return stmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt);
        }
        return false;
    }

    public boolean deleteById(Integer id) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "delete from userinfo where id=?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            return stmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt);
        }
        return false;
    }

    public boolean updateById(UserInfo userInfo) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "update userinfo set username=?,password=? where id=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, userInfo.getUsername());
            stmt.setString(2, userInfo.getPassword());
            stmt.setInt(3, userInfo.getId());
            return stmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt);
        }
        return false;
    }

    public UserInfo selectOneById(Integer id) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from userinfo where id=? limit 1";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            rs = stmt.executeQuery();
            if (rs.next()) {
                UserInfo userInfo = new UserInfo();
                userInfo.setId(rs.getInt("id"));
                userInfo.setUsername(rs.getString("username"));
                userInfo.setPassword(rs.getString("password"));
                return userInfo;
            } else {
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt, rs);
        }
        return null;
    }

    public List<UserInfo> findAll() {
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        List<UserInfo> list = new ArrayList<>();
        try {
            //1.加载驱动
            //2.创建连接
            conn = DBUtil.getConnection();
            //3.写sql语句
            String sql = "select * from userinfo";
            //4.得到statement对象执行sql
            statement = conn.prepareStatement(sql);
            //5.得到结果集
            rs = statement.executeQuery();
            //6.处理结果集
            while (rs.next()) {
                UserInfo userInfo = new UserInfo();
                userInfo.setId(rs.getInt(1));
                userInfo.setUsername(rs.getString(2));
                userInfo.setPassword(rs.getString(3));
                list.add(userInfo);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //7.关闭资源
            DBUtil.close(conn, statement, rs);
        }
        return list;
    }

}

测试类代码：

public class Test {
    public static void main(String[] args) {
        try {
            UserInfoDao userInfoDao = new UserInfoDao();
            //list
            List<UserInfo> users = userInfoDao.findAll();
            System.out.println(users);
            //Insert
            UserInfo userInfo = new UserInfo("hualili", "asdf");
            userInfoDao.add(userInfo);
            //list
            users = userInfoDao.findAll();
            System.out.println(users);
            //delete
            userInfoDao.deleteById(1);
            //list
            System.out.println(userInfoDao.findAll());
            //update
            userInfoDao.updateById(new UserInfo("中软", "国际").setId(3));
            //list
            System.out.println(userInfoDao.findAll());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

结果截图：