JDBC学习笔记(3)——复习和练习
复习和练习
复习部分
一、获取数据库连接
1)方式一
// 获取数据库连接
@Test
public void testGetConnection() throws Exception {
// 1.准备获取连接的四个字符串:user,jdbcurl,password,driverClass
String user = "root";
String password = "123456";
String jdbcUrl = "jdbc:mysql://localhost:3306/atguigu";
String driverClass = "com.mysql.jdbc.Driver";
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(jdbcUrl, user,
password);
System.out.println(connection);
}
2)方式二：解耦合
public Connection getConnection() throws IOException,
ClassNotFoundException, SQLException {
Properties properties = new Properties();
InputStream inputStream = JDBCTest.class.getClassLoader()
.getResourceAsStream("jdbc.properties");
properties.load(inputStream);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String jdbcUrl = properties.getProperty("jdbcUrl");
String driverClass = properties.getProperty("driver");
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(jdbcUrl, user,
password);
return connection;
}
二、Statement执行更新数据的操作
我们这里把更新数据的操作。用一个通用的update函数来表示
public void update() {
// 1.获取数据库连接
Connection connection = null;
Statement statement = null;
try {
connection=getConnection();
// 2.调用Connection对象的createStatement()方法获取
// Statement对象
statement=connection.createStatement();
// 3.准备SQL语句
String sql="insert into examstudent values(1,4,'412824195263214584','200523164754000','张峰','郑州',85)";
// 4.发送SQL语句：调用Statement对象的excuteUpdate(sql)方法
statement.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}finally{
// 5.关闭数据库资源：由里向外关闭
releaseDB(null, statement, connection);
}
}
三、ResultSet执行查询操作
@Test
public void testResultSet(){
Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
try {
//1.获取数据库连接
connection=getConnection();
//2.Statement
statement=connection.createStatement();
//3.准备SQL
String sql="select * from customers";
//4.执行SQL,得到结果集
resultSet=statement.executeQuery(sql);
//5.处理结果集
while(resultSet.next()){
int id=resultSet.getInt(1);
String name=resultSet.getString(2);
String email=resultSet.getString(3);
Date birth=resultSet.getDate(4);
System.out.println(id);
System.out.println(name);
System.out.println(email);
System.out.println(birth);
System.out.println("----------");
}
} catch (Exception e) {
e.printStackTrace();
}finally{
releaseDB(resultSet, statement, connection);
}
}
四、工具类的使用JDBCTools
将获取连接、执行更新操作、释放资源封装在一个JDBCTools类中
package com.atguigu.jdbc;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCTools {
// 更新的方法:插入、删除、更新，但是不包含select
public static void update(String sql) {
// 1.获取数据库连接
Connection connection = null;
Statement statement = null;
try {
connection = getConnection();
// 2.调用Connection对象的createStatement()方法获取
// Statement对象
statement = connection.createStatement();
// 4.发送SQL语句：调用Statement对象的excuteUpdate(sql)方法
statement.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
// 5.关闭数据库资源：由里向外关闭
release(null, statement, connection);
}
}
// 获取数据库连接
public static Connection getConnection() throws IOException,
ClassNotFoundException, SQLException {
Properties properties = new Properties();
InputStream inputStream = JDBCTest.class.getClassLoader()
.getResourceAsStream("jdbc.properties");
properties.load(inputStream);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String jdbcUrl = properties.getProperty("jdbcUrl");
String driverClass = properties.getProperty("driver");
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(jdbcUrl, user,
password);
return connection;
}
// 释放数据库资源
public static void release(ResultSet rs, Statement statement,
Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
// TODO: handle exception
}
}
if (statement != null) {
try {
statement.close();
} catch (Exception e2) {
// TODO: handle exception
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
练习部分
我们的练习是这样的：
新建一个表：
向表中插入数据：
插入成功的话会出现这样的提示(我们从控制台获取要插入的数据):
第一步、用图形化界面SQLyog创建一个数据表examstudent
第二步、向数据表中插入数据
面向对象的思想去编程：将插入的数据封装在一个类Student中
package com.atguigu.jdbc;
public class Student {
// 流水号
private int flowId;
// 考试类型
private int type;
// 身份证号
private String idCard;
// 准考证号
private String examCard;
// 学生姓名
private String studentName;
// 学生地址
private String location;
// 考试成绩
private int grade;
public int getFlowId() {
return flowId;
}
public void setFlowId(int flowId) {
this.flowId = flowId;
}
public int getType() {
return type;
}
public void setType(int type) {
this.type = type;
}
public String getIdCard() {
return idCard;
}
public void setIdCard(String idCard) {
this.idCard = idCard;
}
public String getExamCard() {
return examCard;
}
public void setExamCard(String examCard) {
this.examCard = examCard;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public String getLocation() {
return location;
}
public void setLocation(String location) {
this.location = location;
}
public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
public Student(int flowId, int type, String idCard, String examCard,
String studentName, String location, int grade) {
super();
this.flowId = flowId;
this.type = type;
this.idCard = idCard;
this.examCard = examCard;
this.studentName = studentName;
this.location = location;
this.grade = grade;
}
public Student() {
}
// 重写Student对象的toString函数
@Override
public String toString() {
return "Student [flowId=" + flowId + ", type=" + type + ", idCard="
+ idCard + ", examCard=" + examCard + ", studentName="
+ studentName + ", location=" + location + ", grade=" + grade
+ "]";
}
}
从控制台获取输入信息作为插入student对象的相应的字段值，并插入到新创建的表中
package com.atguigu.jdbc;
import java.util.Scanner;
import org.junit.Test;
public class JDBCTestCase {
//单元测试
@Test
public void testAddNewStudent() {
Student student = getStudentFromConsole();
addNewStudent(student);
}
// 从控制台输入学生的信息
private Student getStudentFromConsole() {
Scanner scanner = new Scanner(System.in);
Student student = new Student();
System.out.print("FlowId:");
student.setFlowId(scanner.nextInt());
System.out.print("Type:");
student.setType(scanner.nextInt());
System.out.print("IdCard:");
student.setIdCard(scanner.next());
System.out.print("ExamCard:");
student.setExamCard(scanner.next());
System.out.print("StudentName:");
student.setStudentName(scanner.next());
System.out.print("Location:");
student.setLocation(scanner.next());
System.out.print("Grade:");
student.setGrade(scanner.nextInt());
return student;
}
public void addNewStudent(Student student) {
String sql = "insert into examstudent" + " values("
+ student.getFlowId() + "," + student.getType() + ",'"
+ student.getIdCard() + "','" + student.getExamCard() + "','"
+ student.getStudentName() + "','" + student.getLocation()
+ "'," + student.getGrade() + ")";
System.out.println(sql);
JDBCTools.update(sql);
}
}
第三步、进行数据的查询(按第三张图片中要求的形式进行查询)
1).从控制台输入一个整数，确定要查询的类型
/*
* 1.身份证查询。 2.用准考证查询 。 3，其他，重新输入
*/
private int getSearchTypeFromConsole() {
System.out.println("请输入查询类型:1.身份证查询. 2.用准考证查询 ");
Scanner scanner = new Scanner(System.in);
int type = scanner.nextInt();
if (type != 1 && type != 2) {
System.out.println("输入有误，请重新输入！");
throw new RuntimeException();
}
return 0;
}
2).根据查询类型准备sql语句
// searchType:1或者2
private Student searchStudent(int searchType) {
String sql = "select * from examstudent where ";
Scanner scanner = new Scanner(System.in);
// 1.根据输入的searchType,提示用户输入信息
// 1.1若searchType=1,提示：请输入身份证号
// 1.2若searchType=2,提示：请输入准考证号
// 2/根据searchType确定SQL
if (searchType == 1) {
System.out.print("请输入准考证号：");
String examCard = scanner.next();
sql = sql + "examCard='" + examCard + "'";
} else {
System.out.print("请输入身份证号：");
String IdCard = scanner.next();
sql = sql + "IdCard='" + IdCard + "'";
}
// 3.执行查询
Student student = getStudent(sql);
// 4.若存在结果，将查询结果封装成一个Student对象
return student;
}
执行查询操作，将结果封装成一个Student对象
private Student getStudent(String sql) {
Student student = null;
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCTools.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
student = new Student(resultSet.getInt(1), resultSet.getInt(2),
resultSet.getString(3), resultSet.getString(4),
resultSet.getString(5), resultSet.getString(6),
resultSet.getInt(7));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.release(resultSet, statement, connection);
}
return student;
}
打印查询结果：
/**
* 打印学生信息：若学生存在则打印具体信息，否则打印：查无此人
*/
private void printStudent(Student student) {
if (student != null) {
System.out.println(student);
} else {
System.out.println("查无此人");
}
}
写一个测试方法测试一下：
@Test
public void testGetStudent() {
// 1.得到查询的类型
int searchType = getSearchTypeFromConsole();
// 2.具体查询信息
Student student = searchStudent(searchType);
// 3.打印学生信息
printStudent(student);
}
运行结果：
请输入查询类型:1.身份证查询. 2.用准考证查询 1 请输入身份证号：3 Student [flowId=1, type=2, idCard=3, examCard=4, studentName=lili, location=dalin, grade=85]
本文为博主原创文章，转载请注明出处：http://www.cnblogs.com/ysw-go/
1、本博客的原创原创文章，都是本人平时学习所做的笔记，如有错误，欢迎指正。
2、如有侵犯您的知识产权和版权问题，请通知本人，本人会即时做出处理文章。
3、本博客的目的是知识交流所用，转载自其它博客或网站，作为自己的参考资料的，感谢这些文章的原创人员
