JDBC的基础创建和封装使用_pstm.setstring(1,"zs");-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_45166354/article/details/101451113

JDBC的基础创建

首先确认要使用的数据库。
在项目src中创建lib文件夹，导入需要使用的jar包。

mysql-connector-java-5.0.8-bin.jar

在这里插入图片描述
将jar包添加到配置中。
右击jar包，选择 Add as Libray

点击ok

jar包下存在配置，即添加完成。

import java.sql.*;

public class JDBC {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        //1.加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //2.创建连接														shop为数据库
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/shop?useSSL=true&characterEncoding=utf-8&&user=root&&password=123456");
            System.out.println("数据库连接成功！"+conn);
            //3.编写sql语句
            String sql = "select * from adminuser";
            //4.创建存放sql语句的对象
            pstm = conn.prepareStatement(sql);
            //5.执行sql，并得到返回结果
            rs = pstm.executeQuery();
            //6.遍历结果集
            while (rs.next()){
                System.out.println("id为："+rs.getInt(1));
                System.out.println("用户名为："+rs.getString(2));
                System.out.println("密码为："+rs.getString(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if(rs!= null){
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
            }
            if (pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

useSSL=true

作用：当前版本的MySQL需要指明是否进行SSL连接。
当你的JDBC版本与MySQL版本不兼容，MySQL的版本更高时，在连接语句后添加上useSSL=true就可以连接到数据库了。

JDBC的封装使用

import java.sql.*;

public class DB {
//    封装jdbc
    private static String Driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/shop?useSSL=true&characterEncoding=utf-8";
    private static String user = "root";
    private static String password = "123456";

    static {
        try {
            Class.forName(Driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //数据库的连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }
    //关闭数据库
    public static void get_CloseConn(ResultSet rs, PreparedStatement pstm,Connection coon) throws SQLException {
        if (rs!= null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        if (coon != null){
            coon.close();
        }
    }
}

adminuser表的实体类创建。

public class UserInfo {
    private int uid;
    private String username;
    private String password;

//    get和set访问器
    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    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 UserInfo(int uid, String username, String password) {
        this.uid = uid;
        this.username = username;
        this.password = password;
    }

    public UserInfo() {
    }

    @Override
    public String toString() {
        return "UserInfo{" +
                "uid=" + uid +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

实现增加功能。

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDao {
    Connection coon = null;
    PreparedStatement pstm = null;
    ResultSet rs = null;
    public void addUser(UserInfo userInfo){
        //1.创建连接数据库
        try {
            coon = DB.getConnection();
            //2.创建存放sql语句的对象
            pstm = coon.prepareStatement("insert into adminuser (uid,username,password)values (?,?,?)");
            pstm.setInt(1,userInfo.getUid());
            pstm.setString(2,userInfo.getUsername());
            pstm.setString(3,userInfo.getPassword());
            //执行sql语句，并得到返回结果
            int i = pstm.executeUpdate();
            if (i>0){
                System.out.println("增加成功");
            }else {
                System.out.println("增加失败");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                DB.get_CloseConn(null,pstm,coon);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

删除功能

@Override
    public void delete(int id) {
        try {
            connection = DBUtils.getConnection();
            pstm = connection.prepareStatement("DELETE from user where id = "+id);
            id = pstm.executeUpdate();
            if (id>0)
            {
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                DBUtils.get_CloseConn(rs,pstm,connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

修改功能

@Override
    public void update(User user) {
        try {
            connection = DBUtils.getConnection();
            pstm = connection.prepareStatement("UPDATE USER set username = ? where id = ?");
            pstm.setString(1,user.getUsername());
            pstm.setInt(2,user.getId());
            int i = pstm.executeUpdate();
            if (i>0){
                System.out.println("修改成功");
            }else {
                System.out.println("修改失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                DBUtils.get_CloseConn(rs,pstm,connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

查询所有

@Override
    public List<User> findAll() {
        //多态
        List<User> UserList = new ArrayList<>();
        try {
            connection = DBUtils.getConnection();
            pstm = connection.prepareStatement("SELECT * from USER ");
            rs = pstm.executeQuery();
            while (rs.next()){
                user = new User();
                //下标从1开始
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                UserList.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                DBUtils.get_CloseConn(rs,pstm,connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return UserList;
    }

条件查询

@Override
    public User findId(int id) {
        User user1 = null;
        try {
            conn = DBUtil.get_Conn();
            pstm = conn.prepareStatement("select * from user where id = ?");
            pstm.setInt(1,id);
            rs = pstm.executeQuery();
            while (rs.next()){
                user1 = new User();
                user1.setId(rs.getInt("id"));
                user1.setUsername(rs.getString("username"));
                user1.setPassword(rs.getString("password"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                DBUtil.get_CloseConn(rs, pstm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user1;
    }