JDBC实验

xiaoxiao2021-02-28 49

一、JDBC单表记录的增删改查已知：建立表student，并向表里插入几条记录，

create table student(

id int PRIMARY KEY auto_increment,

name varchar(20) not null,

schoolin date not null,

score float not null);

insert into student values(null,’李丽’,’2015-09-01’,86);

insert into student values(null,’王五’,’2016-09-01’,99);

insert into student values(null,’张三’,’2014-09-01’,88);

要求：用JAVA程序实现如下功能：

1、向表中增加记录并显示所有记录（数据自己指定）；

2、从表中删除id=1的记录并显示所有记录；

3、修改表中记录：查询条件id=2，将name修改为：山东理工，修改完毕显示所有记录；

4、查询表中id=3的记录并显示；

1、向表中增加记录并显示所有记录（数据自己指定）

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", ""); //System.out.println(con); // insert String insertSQL="insert into student values(null,?,?)"; PreparedStatement pst1 = con.prepareStatement(insertSQL); pst1.setString(1,"李四"); pst1.setInt(2, 25); int result=pst1.executeUpdate(); if(result==1) { System.out.println("插入记录成功！"); } //查询 ResultSet rs = pst.executeQuery(); while(rs.next()) { int id=rs.getInt(1); String name=rs.getString(2); int age=rs.getInt(3); System.out.println(id+"--"+name+"--"+age); } //7 关闭资源，关闭连接 pst3.close(); rs.close(); pst.close(); con.close(); } }

2、从表中删除id=1的记录并显示所有记录；

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", ""); //System.out.println(con); // delete String deleteSQL="delete from student where id=?"; PreparedStatement pst2 = con.prepareStatement(deleteSQL); pst2.setInt(1, 1); int result1=pst2.executeUpdate(); if(result1==1) { System.out.println("删除记录成功！"); } //6 查询 ResultSet rs = pst.executeQuery(); while(rs.next()) { int id=rs.getInt(1); String name=rs.getString(2); int age=rs.getInt(3); System.out.println(id+"--"+name+"--"+age); } //7 关闭资源，关闭连接 pst3.close(); rs.close(); pst.close(); con.close(); } }

3、修改表中记录：查询条件id=2，将name修改为：山东理工，修改完毕显示所有记录；

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", ""); //System.out.println(con); //5 update String updateSQL="update student set name=? where id=?"; PreparedStatement pst3 = con.prepareStatement(updateSQL); pst3.setString(1,"山东理工"); pst3.setInt(2, 2);//id为2.where后面是条件 int result=pst3.executeUpdate(); if(result==1) { System.out.println("修改记录成功！"); } String sql="select * from student"; PreparedStatement pst = con.prepareStatement(sql); //6 查询 ResultSet rs = pst.executeQuery(); while(rs.next()) { int id=rs.getInt(1); String name=rs.getString(2); int age=rs.getInt(3); System.out.println(id+"--"+name+"--"+age); } //7 关闭资源，关闭连接 pst3.close(); rs.close(); pst.close(); con.close(); } }

4、查询表中id=3的记录并显示；

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", ""); //System.out.println(con); //根据主键（id）来查询记录 String sql = "select * from student where id = ?"; pst = con.prepareStatement(sql); ResultSet rs = pst.executeQuery(); pst.setInt(1, 3);//id为3 //查到或者没查到 Student stu = null; if(rs.next()){ stu = new Student(rs.getInt(1), rs.getString(2), rs.getInt(3)); } //7 关闭资源，关闭连接 pst3.close(); rs.close(); pst.close(); con.close(); } }

二、JDBC面向对象方式实现数据库CRUD操作。（30分）

已知：建立表student，并向表里插入几条记录。

create table student(

id int PRIMARY KEY auto_increment,

name varchar(20) not null,

schoolin date not null,

score float not null);

insert into student values(null,’李丽’,’2015-09-01’,86);

insert into student values(null,’王五’,’2016-09-01’,99);

insert into student values(null,’张三’,’2014-09-01’,88);

基本要求：将表操作封装成类，将功能封装成类的方法。

功能要求：

（1）向表中增加记录并显示所有记录（数据自己指定）；

（2）从表中删除id=1的记录并显示所有记录；

（3）修改表中记录：查询条件id=2，将name修改为：山东理工，修改完毕显示所有记录；

（4）查询表中id=3的记录并显示。

学生类：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; } }

1、向表中增加记录并显示所有记录（数据自己指定）；学生表操作类：StudentDao.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 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 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; }

测试类：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("插入记录成功！"); } //显示所有记录 List<Student> stuList=dao.queryAll(); for(Student stu:stuList) { System.out.println(stu); } System.out.println("======="); } }

2、从表中删除id=1的记录并显示所有记录；

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 deleteStudent(int id) throws ClassNotFoundException, SQLException{ con = getConn(); String sql = "delete from student where id=?";//准备sql语句 pst = con.prepareStatement(sql);//准备sql语句的对象 //给？赋值 pst.setInt(1, 1);//删除的条件是id=1 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; } 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(); int result2 = dao.delStudent(1); if(result2==1) { System.out.println("删除记录成功！"); } List<Student> stuList=dao.queryAll(); for(Student stu:stuList) { System.out.println(stu); } System.out.println("======="); } }

3、修改表中记录：查询条件id=2，将name修改为：山东理工，修改完毕显示所有记录；

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 updateStudent() throws ClassNotFoundException, SQLException{ con = getConn(); String sql = "update student set name = ? where id = ?";//准备sql语句 pst = con.prepareStatement(sql);//准备sql语句的对象 //给？赋值 pst.setString(1, "山东理工"); pst.setInt(2, 2);//修改id为2的name 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; } 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(); int result3 = dao.updateStudent(); if(result3==1) { System.out.println("修改记录成功！"); } List<Student> stuList=dao.queryAll(); for(Student stu:stuList) { System.out.println(stu); } System.out.println("======="); } }

4、查询表中id=3的记录并显示。

转载请注明原文地址: https://www.6miu.com/read-2627433.html

技术

最新回复(0)