实验七Java之Jdbc.doc
精品文档，仅供学习与交流，如有侵权请联系网站删除实验七 Jdbc编程1实验目的（1）掌握通过JDBC方式操作数据库的基本步骤。（2）掌握增、删、改、查记录等的方法。（3）掌握查询记录以及遍历查询结果的方法。2实验内容实验题1 学生信息管理。创建student表，包含学生的学号、姓名、年龄信息。 根据学号，可以查询到学生的姓名和年龄； 给定学生的学号、姓名、年龄，在表中追加一行信息； 给定学生的学号，可以从表中删除该学生的信息；基本要求 对上面的每一个功能编写相应的函数，并测试。1.创建Student表package .jp.text;import java.util.Scanner;import .jp.data.ConnectionBean;import .jp.data.ConnectionWay;public class text * param argspublic static void main(String args) ConnectionBean cb = new ConnectionBean();/加载驱动ConnectionWay cw = new ConnectionWay();Scanner scan = new Scanner(System.in);String number = null; String name = null; String age = null; /调用方法测试： number = scan.next(); name = scan.next(); age = scan.next(); cw.numberQuery(number); cw.insert("2019", name, age); cw.delete(number); / 关闭连接： cw.free();2.增删改查package .jp.data;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class ConnectionWay Connection conn = ConnectionBean.getConnection();/连接PreparedStatement pst = null;ResultSet rs = null;public void numberQuery(String number) String sql = "select Sname,Sage from S where number =?"try pst = conn.prepareStatement(sql);pst.setString(1,number);rs = pst.executeQuery();while(rs.next()System.out.println(rs.getString("Sname") + " " + rs.getString("Sage"); catch (SQLException e1) / TODO Auto-generated catch blocke1.printStackTrace();public void insert(String number,String Sname,String Sage) int col = 0;String sql = "insert into S values(?,?,?)"try pst = conn.prepareStatement(sql);pst.setString(1, number);pst.setString(2, Sname);pst.setString(3,Sage);col = pst.executeUpdate();if(col = 1)System.out.println("插入数据成功！"); catch (SQLException e1) / TODO Auto-generated catch blocke1.printStackTrace();public void delete(String number) int col = 0;String sql = "delete from S where number = ?"try pst = conn.prepareStatement(sql);pst.setString(1, number);col = pst.executeUpdate();if(col != 0)System.out.println("删除成功!"); catch (SQLException e1) / TODO Auto-generated catch blocke1.printStackTrace();public void free()ConnectionBean.free(rs, pst, conn);3.遍历package .jp.data;import java.sql.PreparedStatement;import java.sql.Statement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;public class ConnectionBean private static String url = "jdbc:mysql:/localhost:3306/Student"private static String user = "root"private static String passWord = " "static try Class.forName("com.mysql.jdbc.Driver"); catch (ClassNotFoundException e) / TODO Auto-generated catch blocke.printStackTrace();public static Connection getConnection() Connection conn = null;try conn = (Connection) DriverManager.getConnection(url, user,null /*, passWord*/); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();return conn;public static void free(ResultSet rs, Statement st, Connection conn) try rs.close(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace(); finally try st.close(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace(); finally try conn.close(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();实验题2 Jdbc编程。在MySql数据库的test库中，建一个表student，其内容如下表所示，将表中score大于60的记录的信息输出到控制台。 学生信息表IdNameGenderScore1002Tommale801003Maryfemale891004Petermale541005Johnmale761006polofemale90package .jp.text;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import .jp.ConnectionBean;public class text * param argspublic static void main(String args) Connection conn = null;PreparedStatement pst = null;ResultSet rs = null;conn = ConnectionBean.getConnection();try float score = 0f;pst = conn.prepareStatement("select * from student");rs = pst.executeQuery();while (rs.next() score = rs.getFloat("Score");if (score > 60) System.out.println(rs.getObject(1) + "," + rs.getObject(2)+ "," + rs.getObject(3) + "," + rs.getObject(4); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();ConnectionBean.free(rs, pst, conn);package .jp;import java.sql.PreparedStatement;import java.sql.Statement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;public class ConnectionBean private static String url = "jdbc:mysql:/localhost:3306/student"private static String user = "root"private static String passWord = null;static try Class.forName("com.mysql.jdbc.Driver"); catch (ClassNotFoundException e) / TODO Auto-generated catch blocke.printStackTrace();public static Connection getConnection() Connection conn = null;try conn = (Connection) DriverManager.getConnection(url, user, passWord); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();return conn;public static void free(ResultSet rs, Statement st, Connection conn) try rs.close(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace(); finally try st.close(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace(); finally try conn.close(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();*实验题3 (选作题) 熟悉课堂内容，学习掌握数据库访问编程实现的程序结构。（1）设计一个与连接数据库相关接口UserDao（访问数据库增、删、改、查DAO（Data Access Object，数据访问对象），一般针对不同的数据库表采用不同的DAO功能实现类。）（2）设计持久实体类（PO）封装装数据库表中的各个字段（3）设计一个类，实现UserDao接口，完成访问数据库的相关操作（增、删、改、查）；（4）设计一个类，测试UserDao的实现类。（5）修改daoconfig.properties文件，切换UserDao的实现。4实验总结 本次实验练习了JAVA数据库的连接及应用.先建立连接,加载驱动,然后添加记录,创建表格.接下来练习了数据库的增删改查。熟悉过基本操作后，学习了查询记录以及遍历查询结果的方法。 通过这次实验，加深了自己对数据库的理解。但自己操作过于生疏，需要多加练习。【精品文档】第 8 页
