JDBC连接以及使用

学习目标：

我们要使用工厂地方式来使用数据库连接，然后使用工厂来进行数据库地存储

• 掌握jdbc连接，使用静态方法
• 掌握jdbc的增删改查

---

学习内容：

如果我们将数据库的连接设置为静态方法，那么，我们将不能释放

1. 连接数据库

public class ConnectionFactory {

    //连接对象
    private static Connection conn=null;
    //数据库的名称
    //如果是5.x 那么这里就没有cj
    private static String driverName= "com.mysql.cj.jdbc.Driver";
    //连接地址
    private static String url ="jdbc:mysql://localhost:3306/smbms";
    //数据库的名称
    private static String username="root";
    //数据库的密码
    private static String password="root";

    private ConnectionFactory(){}
    //获取连接对像
    public static Connection getConnection(){
        if (conn==null) {
            try {
                //1.加载驱动
                Class.forName(driverName);
                //2.获取数据库连接对象
                conn=DriverManager.getConnection(url,username,password);
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        return conn;
    }

}

2. jdbc查询

		Connection connection = null;
        ResultSet rs=null;
        Statement stm=null;
        User user=null;
//        String sql = "select * from smbms_user where userCode='admin' and userPassword='1234567' ";
        String sql = "select * from smbms_user where userCode= '"+uname+"' and userPassword= '"+passwd+"'";
        try {
            connection = ConnectionFactory.getConnection();
            stm=connection.createStatement();
            rs=stm.executeQuery(sql);
            //循环得到每一条数据
            while (rs.next()){
                user = new User();
                //注意这里的getString里面的参数必须和数据库保持一致
                //这里需要有自己的用户对象，我这里是user
                user.setId(rs.getInt("id"));
                //将得到的数据添加到用户中
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setUserPassword(rs.getString("userPassword"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                user.setUserRole(rs.getInt("userRole"));
                user.setCreatedBy(rs.getInt("createdBy"));
                user.setCreationDate(rs.getTimestamp("creationDate"));
                user.setModifyBy(rs.getInt("modifyBy"));
                user.setModifyDate(rs.getTimestamp("modifyDate"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stm!=null){
                try {
                    stm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

3. jdbc的添加

		Connection connection = null;
        ResultSet rs=null;
        Statement stm=null;
        String sql = "insert into smbms_user (userCode, userPassword) values ('" + uname + "', '" + passwd + "')";
        try {
            connection = ConnectionFactory.getConnection();
            stm=connection.createStatement();
            int rst=stm.executeUpdate(sql);
            if (rst>0){
                System.out.println("数据增添成功");
            }
            else
                System.out.println("数据增添失败");
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stm!=null){
                try {
                    stm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

4. jdbc的删除

		Connection connection=null;
        ResultSet rs=null;
        Statement stm=null;
        String sql = "delete from smbms_user where id='01'";
        try {
            connection = ConnectionFactory.getConnection();
            stm=conn.createStatement();
            int rst=stm.executeUpdate(sql);
            if (rst>0){
                System.out.println("数据删除成功");
            }
            else
                System.out.println("数据删除失败");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stm!=null){
                try {
                    stm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

5. jdbc的修改

		Connection connection=null;
        ResultSet rs=null;
        Statement stm=null;
        String sql="update smbms _user set age='50' where id='03'";
        try {
            connection = ConnectionFactory.getConnection();
            stm=conn.createStatement();
            int rst=stm.executeUpdate(sql);
            if (rst>0){
                System.out.println("数据修改成功");
            }
            else
                System.out.println("数据修改失败");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stm!=null){
                try {
                    stm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

---

提示：切记如果使用了static修饰了工厂中的连接方法，我们在后续使用jdbc操作数据库时一定不要释放连接，否者会报No operations allowed after connection closed的错误