JDBC知识点

一、 连接数据库查询数据库中数据

String url="jdbc:mysql://localhost:3306/pet?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8";
String userName="root";
String password="123456";

1.加载驱动

  Class.forName("com.mysql.cj.jdbc.Driver");

2.获取连接

 Connection conn= DriverManager.getConnection(url,userName,password);

3.创建Starement

  Statement statement=conn.createStatement();

4.执行sql语句，并用ResultSet接受查询结果

 ResultSet rs=statement.executeQuery("select * from pet ");

5.查看查询结果 

 while (rs.next()){
                 int id=rs.getInt(1);
                String name=rs.getString("name");
                String type_name=rs.getString("type_name");
                System.out.println("id:"+id+"\t name:"+name+"\ttype_name:"+type_name);
            }

6.释放资源

rs.close();
statement.close();
conn.close();

二、连接数据库方法

//配置文件database.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/pet?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8
user=root
password=123456

public class DBUtil {
    static  String driver;//数据库驱动
    static  String url;
    static  String user;//数据库用户名
    static  String password;//数据库密码

    static {//静态代码块，在类加载的时候
        init();
    }
    public  static  void  init(){//初始化连接参数，从配置文件里获得
        Properties params=new Properties();
        String configFile="database.properties";//配置文件路径
        InputStream is=DBUtil.class.getClassLoader().getResourceAsStream(configFile);//加载配置文件到输入流中
        try {
            params.load(is);//从输入流中读取属性列表
        } catch (IOException e) {
            e.printStackTrace();
        }
        //获取指定的属性名获取对应的值
        driver=params.getProperty("driver");
        url=params.getProperty("url");
        user=params.getProperty("user");
        password=params.getProperty("password");
    }

    public  static Connection getConn(){
        Connection conn=null;

        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            conn= DriverManager.getConnection(url,user,password);
            if(conn!=null){
                System.out.println("数据库连接成功");
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
        return conn;
    }
    //关闭资源
    public  static  void  classAll(Connection conn, Statement statement, ResultSet rs){
        try {
            if(rs!=null){
                rs.close();
            }
            if(statement!=null){
                statement.close();
            }
            if(conn!=null){
                conn.close();
            }
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}

二、JDBC实现增删改查

1.数据库表

id    	int  //主键  自动递增
name	varchar
age	    int
bir	    date
phone	varchar
role	int

2.创建构造方法

放到bean包

public class Userinfo {
    private  int id;
    private  String name;
    private  String password;
    private  int age;
    private  String bir;
    private  String phone;

    public Userinfo() {
    }

    public Userinfo(int id, String name, String password, int age, String bir, String phone) {
        this.id = id;
        this.name = name;
        this.password = password;
        this.age = age;
        this.bir = bir;
        this.phone = phone;
    }
    public Userinfo(String name, String password, int age, String bir, String phone) {
        this.id = id;
        this.name = name;
        this.password = password;
        this.age = age;
        this.bir = bir;
        this.phone = phone;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

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

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getBir() {
        return bir;
    }

    public void setBir(String bir) {
        this.bir = bir;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}

 2.提供接口实现接口

   接口放到dao包中

 

//接口
public interface UserinfoDao {
    //登录
    Userinfo getUserByPwd(String name,String password);
    //添加用户信息
    boolean inserUser(Userinfo userinfo);
    //删除用户信息
    boolean deleteUser(int id);
    //修改用户信息
    boolean updateUser(Userinfo userinfo);
    //查询用户列表
    List<Userinfo> getUserList();
    //根据id查询单个用户
    Userinfo getUserById(int id);
}

实现接口

在dao.impl包中

public class UserinfoDaoImpl implements com.dao.UserinfoDao {
    @Override
    public Userinfo getUserByPwd(String name, String password) {
        Userinfo userinfo=null;
        Connection conn=null;
        PreparedStatement pStatement=null;
        ResultSet rs=null;
        try {
        conn= DBUtil.getConn();
        String sql="select * from userinfo where name=? and password=?;";
        pStatement=conn.prepareStatement(sql);
        pStatement.setString(1,name);
        pStatement.setString(2,password);
        rs=pStatement.executeQuery();
        if (rs.next()){
            int id=rs.getInt("id");
            int age=rs.getInt("age");
            userinfo=new Userinfo(id,name,password,age,rs.getString("bir"),rs.getString("phone"));
        }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.classAll(conn,pStatement,rs);
        }

        return userinfo;
    }

    @Override
    public boolean inserUser(Userinfo userinfo) {
        boolean flag=false;
        Connection conn=null;
        PreparedStatement pStatement=null;
        conn=DBUtil.getConn();
        String sql="insert into userinfo(name,password,age,bir,phone)value (?,?,?,?,?)";
        try {
            pStatement=conn.prepareStatement(sql);
            pStatement.setString(1,userinfo.getName());
            pStatement.setString(2,userinfo.getPassword());
            pStatement.setInt(3,userinfo.getAge());
            pStatement.setString(4,userinfo.getBir());
            pStatement.setString(5,userinfo.getPhone());
            int rowNum=pStatement.executeUpdate();
            if (rowNum==1){
                flag=true;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.classAll(conn,pStatement,null);
        }
        return flag;
    }

    @Override
    public boolean deleteUser(int id) {
        boolean flag=false;
        Connection conn=null;
        PreparedStatement pStatement=null;
        conn=DBUtil.getConn();
        String sql="delete from userinfo where id=?;";
        try {
            pStatement=conn.prepareStatement(sql);
            pStatement.setInt(1,id);
            int rowNum=pStatement.executeUpdate();
            if (rowNum==1){
                flag=true;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.classAll(conn,pStatement,null);
        }
        return flag;
    }

    @Override
    public boolean updateUser(Userinfo userinfo) {
        boolean flag=false;
        Connection conn=null;
        PreparedStatement pStatement=null;
        conn=DBUtil.getConn();
        String sql="UPDATE userinfo SET name=?,password=?,age=?,bir=?,phone=? where id=?";
        try {

            pStatement=conn.prepareStatement(sql);
            pStatement.setString(1,userinfo.getName());
            pStatement.setString(2,userinfo.getPassword());
            pStatement.setInt(3,userinfo.getAge());
            pStatement.setString(4,userinfo.getBir());
            pStatement.setString(5,userinfo.getPhone());
            pStatement.setInt(6,userinfo.getId());
            int rowNum=pStatement.executeUpdate();
            if (rowNum==1){
                flag=true;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.classAll(conn,pStatement,null);
        }
        return flag;
    }

    @Override
    public List<Userinfo> getUserList() {
        List<Userinfo> userinfos=new ArrayList<Userinfo>();
        Connection conn=null;
        Statement statement=null;
        ResultSet rs=null;
        try {
            conn=DBUtil.getConn();
            statement=conn.createStatement();
            String sql="select  *  from userinfo ;";
            rs=statement.executeQuery(sql);
            while (rs.next()){
                int id=rs.getInt("id");
                int age=rs.getInt("age");
                Userinfo userinfo=new Userinfo(id,rs.getString("name"),rs.getString("password"),age,rs.getString("bir"),rs.getString("phone"));
                userinfos.add(userinfo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.classAll(conn,statement,rs);
        }

        return userinfos;
    }

    @Override
    public Userinfo getUserById(int id) {
        Userinfo userinfo=null;
        Connection conn=null;
        PreparedStatement pStatement=null;
        ResultSet rs=null;
        try {
            conn= DBUtil.getConn();
            String sql="select * from userinfo where id=? ;";
            pStatement=conn.prepareStatement(sql);
            pStatement.setInt(1,id);
            rs=pStatement.executeQuery();
            if (rs.next()){
                int id1=rs.getInt("id");
                int age=rs.getInt("age");
                userinfo=new Userinfo(id,rs.getString("name"),rs.getString("password"),age,rs.getString("bir"),rs.getString("phone"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.classAll(conn,pStatement,rs);
        }

        return userinfo;
    }
}