JDBC数据库编程——基础、封装

版权声明:正在学习中,有错误欢迎指出一起交流呀感谢!ヾ(✿゚▽゚)ノ https://blog.csdn.net/gx17864373822/article/details/80048635

JDBC:(java database connectivity)是java运行平台的核心类库中的一部分,提供了访问数据库中的API,由一些类和接口组成。
使用JDBC提的API操作数据库:应用程序,使用JDBC,来连接数据库。


1、数据库新建表,插入数据
新建数据库:名字为school
这里写图片描述
列民(表头)
这里写图片描述
这里写图片描述

注意:auto_increment代表自增。只对于int型

插入数据:
这里写图片描述
查看表:
这里写图片描述


将sql语句存在工程中:
建Folder,名字为sql
建File,名字为 数据库ming.sql 。例如:school.sql

这里写图片描述

打开复制进去sql语句:
这里写图片描述

例如:

INSERT into student VALUES (1, ‘abc’, 20);
INSERT INTO student(name, age) VALUES (‘zs’, 20);
INSERT INTO student VALUES (null, ‘abb’, 20);


程序:
查询: 通过列名

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


public class Main{
    public static void main(String[] args) throws ClassNotFoundException, SQLException{
        //1加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2获得数据库的连接
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/school", "root", "");//协议+数据库+端口号+数据库名---------用户名--------密码为空
//        System.out.println(con);



        //3准备执行语句
        String sql = "select * from student";
        PreparedStatement pst = con.prepareStatement(sql);


        //4执行
        ResultSet rs = pst.executeQuery();//结果集为一个二维表
        while(rs.next()){//返回true,代表有数据可以读取
            int id = rs.getInt("id");//参数字段名称为数据库的表头(列的名称)
            String name = rs.getString("name");
            int age = rs.getInt("age");
            System.out.println(id + "--" + name + "--" + age);
        }

        //5关闭资源,关闭连接
        rs.close();
        pst.close();
        con.close();
    }
}

输出:
1–abc–20
2–张三–20
3–abc–20

查询的改进: 通过列的序号

 //4执行
        ResultSet rs = pst.executeQuery();//结果集为一个二维表
        while(rs.next()){//返回true,代表有数据可以读取
            int id = rs.getInt(1);//列是从1开始的
            String name = rs.getString(2);
            int age = rs.getInt(3);
            System.out.println(id + "--" + name + "--" + age);
        }

输出结果同上

增删改:

增:

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


public class Main{
    public static void main(String[] args) throws ClassNotFoundException, SQLException{
        //1加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2获得数据库的连接
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8", "root", "");//协议+数据库+端口号+数据库名---------用户名--------密码为空
        //为了插入汉字,所以需要?useUnicode=true&characterEncoding=utf-8


        //3insert
        //有未知数,用?占位
        String insertSQL = "insert into student values(null, ?, ?)";//准备sql语句
        PreparedStatement pst1 = con.prepareStatement(insertSQL);//准备sql语句的对象

        //给?赋值
        pst1.setString(1, "李四");
        pst1.setInt(2, 25);

        int result = pst1.executeUpdate();//对于增删改用update,返回类型为int型
        if(result == 1){
            System.out.println("插入记录成功!");
        }
        //4delete


        //5update


        String sql = "select * from student";
        PreparedStatement pst = con.prepareStatement(sql);
        //6查询
        ResultSet rs = pst.executeQuery();//对于查询用Query,返回类型为二维表
        while(rs.next()){//返回true,代表有数据可以读取
            int id = rs.getInt(1);//列是从1开始的
            String name = rs.getString(2);
            int age = rs.getInt(3);
            System.out.println(id + "--" + name + "--" + age);
        }

        //7关闭资源,关闭连接
        rs.close();
        pst.close();
        con.close();
    }
}

输出:
插入记录成功!
1–abc–20
2–张三–20
3–abc–20
4–李四–25

删:

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


public class Main{
    public static void main(String[] args) throws ClassNotFoundException, SQLException{
        //1加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2获得数据库的连接
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8", "root", "");//协议+数据库+端口号+数据库名---------用户名--------密码为空


       /* //3insert
        String insertSQL = "insert into student values(null, ?, ?)";//准备sql语句
        PreparedStatement pst1 = con.prepareStatement(insertSQL);//准备sql语句的对象
        //给?赋值
        pst1.setString(1, "李四");
        pst1.setInt(2, 25);

        int result = pst1.executeUpdate();
        if(result == 1){
            System.out.println("插入记录成功!");
        }*/

        //4delete
        String deleteSQL = "delete from student where id=?";//准备要执行sql语句
        PreparedStatement pst2 = con.prepareStatement(deleteSQL);//准备sql语句的对象
        pst2.setInt(1, 1);

        int result1 = pst2.executeUpdate();
        if(result1 == 1){
            System.out.println("删除记录成功!");
        }

        //5update



        //6查询所有记录
        String sql = "select * from student";
        PreparedStatement pst = con.prepareStatement(sql);

        ResultSet rs = pst.executeQuery();//结果集为一个二维表
        while(rs.next()){//返回true,代表有数据可以读取
            int id = rs.getInt(1);//列是从1开始的
            String name = rs.getString(2);
            int age = rs.getInt(3);
            System.out.println(id + "--" + name + "--" + age);
        }

        //7关闭资源,关闭连接
        rs.close();
        pst.close();
        con.close();
    }
}

输出:
删除记录成功!
2–张三–20
3–abc–20
11–李四–25

改(更新):

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


public class Main{
    public static void main(String[] args) throws ClassNotFoundException, SQLException{
        //1加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2获得数据库的连接
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8", "root", "");//协议+数据库+端口号+数据库名---------用户名--------密码为空


       /* //3insert
        String insertSQL = "insert into student values(null, ?, ?)";//准备sql语句
        PreparedStatement pst1 = con.prepareStatement(insertSQL);//准备sql语句的对象
        //给?赋值
        pst1.setString(1, "李四");
        pst1.setInt(2, 25);

        int result = pst1.executeUpdate();
        if(result == 1){
            System.out.println("插入记录成功!");
        }*/

        /*//4delete
        String deleteSQL = "delete from student where id=?";//准备要执行sql语句
        PreparedStatement pst2 = con.prepareStatement(deleteSQL);//准备sql语句的对象
        pst2.setInt(1, 1);

        int result1 = pst2.executeUpdate();
        if(result1 == 1){
            System.out.println("删除记录成功!");
        }
        */

        //5update
        String updateSQL = "update student set name = ?,age = ? where id = ?";
        //?的意思是:待输入的值
        PreparedStatement pst3 = con.prepareStatement(updateSQL);
        pst3.setString(1, "吴振浩");
        pst3.setInt(2, 55);
        pst3.setInt(3, 2);

        int result2 = pst3.executeUpdate();
        if(result2 == 1){
            System.out.println("修改记录成功!");
        }



        //6查询所有记录
        String sql = "select * from student";
        PreparedStatement pst = con.prepareStatement(sql);

        ResultSet rs = pst.executeQuery();//结果集为一个二维表
        while(rs.next()){//返回true,代表有数据可以读取
            int id = rs.getInt(1);//列是从1开始的
            String name = rs.getString(2);
            int age = rs.getInt(3);
            System.out.println(id + "--" + name + "--" + age);
        }

        //7关闭资源,关闭连接
        rs.close();
        pst.close();
        con.close();
    }
}

输出:
修改记录成功!
2–吴振浩–55
3–abc–20
11–李四–25

在navicat可视化里面,新的数据库为:
这里写图片描述


封装:

学生类:Sdutent.java


public class Student {

    private int id;
    private String name;
    private int age;

    public Student(int id, String name, int age) {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public Student() {
        super();
    }

    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
    }

    //因为三个属性是私有的,所有可以通过get和set方法取值,赋值
    //变量设置成私有的,方法设置成公开的!-----类的封装
    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 int getAge() {
        return age;
    }

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


}

学生表操作类:StudentDao.java

package pp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;


public class StudentDao {

    Connection con;
    PreparedStatement pst;

    public Connection getConn() throws SQLException, ClassNotFoundException{
         //1加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2获得数据库的连接
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8", "root", "");//协议+数据库+端口号+数据库名---------用户名--------密码为空

        return con;
    }

    //增
    public int addStudent(Student student) throws ClassNotFoundException, SQLException{
        con = getConn();
        String insertSQL = "insert into student values(null, ?, ?)";//准备sql语句
        pst = con.prepareStatement(insertSQL);//准备sql语句的对象
        //给?赋值
        pst.setString(1, student.getName());
        pst.setInt(2, student.getAge());

        int result = pst.executeUpdate();

        pst.close();
        con.close();
        return result;

    }

    //删
    public int deleteStudent(int id) throws ClassNotFoundException, SQLException{
        con = getConn();
        String sql = "delete from student where id=?";//准备sql语句
        pst = con.prepareStatement(sql);//准备sql语句的对象
        //给?赋值
        pst.setInt(1, id);

        int result = pst.executeUpdate();

        pst.close();
        con.close();
        return result;

    }

    //改
    public int updateStudent(Student student) throws ClassNotFoundException, SQLException{
        con = getConn();
        String sql = "update student set name = ?,age = ? where id = ?";//准备sql语句
        pst = con.prepareStatement(sql);//准备sql语句的对象
        //给?赋值
        pst.setString(1, student.getName());
        pst.setInt(2, student.getAge());

        pst.setInt(3, student.getId());

        int result = pst.executeUpdate();

        pst.close();
        con.close();
        return result;

    }


    //查询所有记录
    public List<Student> queryAll() throws ClassNotFoundException, SQLException{

        con = getConn();
        String sql = "select * from student";
        pst = con.prepareStatement(sql);

        rs = pst.executeQuery();

        List<Student> list = new ArrayList<Student>();//创建链表容器来存放数据

        while(rs.next()) {

            /*int id = rs.getInt(1);//列是从1开始的
            String name = rs.getString(2);
            int age = rs.getInt(3);
            Student stu = new Student(id, name, age);*/

//            Student stu = new Student(rs.getInt(1), rs.getString(2), rs.getInt(3));

            Student stu = new Student();
            stu.setId(rs.getInt(1));
            stu.setName(rs.getString(2));
            stu.setAge(rs.getInt(3));

            list.add(stu);
        }


        return list;
    }


    //根据主键查询记录
    public Student queryById(int id) throws ClassNotFoundException, SQLException{
        con = getConn();
        String sql = "select * from student where id = ?";
        pst = con.prepareStatement(sql);

        ResultSet rs = pst.executeQuery();
        pst.setInt(1, id);

        //查到或者没查到
        Student stu = null;

        if(rs.next()){
            stu = new Student(rs.getInt(1), rs.getString(2), rs.getInt(3));

        }

        return stu;
    }

测试类:Main.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class Main {
 public static void main(String[] args) throws ClassNotFoundException, SQLException  {
  StudentDao dao=new StudentDao();

  Student stu1=new Student(1,"系统",22);
  /*int result = dao.addStudent(stu1);
  if(result==1)
  {
   System.out.println("插入记录成功!");
  }*/

 /* int result2 = dao.delStudent(1);
  if(result2==1)
  {
   System.out.println("删除记录成功!");
  }*/

  Student stu2=new Student(3,"李四",32);
  int result3 = dao.updateStudent(stu2);
  if(result3==1)
  {
   System.out.println("修改记录成功!");
  }

  List<Student> stuList=dao.queryAll();
  for(Student stu:stuList)
  {
   System.out.println(stu);
  }
  System.out.println("=======");

  Student stu = dao.queryById(100);
  System.out.println(stu);

 }
}
展开阅读全文

没有更多推荐了,返回首页