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();
}
}
}
}

练习部分

我们的练习是这样的:

新建一个表:

JDBC学习笔记(3)——复习和练习

向表中插入数据:

JDBC学习笔记(3)——复习和练习

插入成功的话会出现这样的提示(我们从控制台获取要插入的数据):

JDBC学习笔记(3)——复习和练习

第一步、用图形化界面SQLyog创建一个数据表examstudent

JDBC学习笔记(3)——复习和练习

第二步、向数据表中插入数据

面向对象的思想去编程:将插入的数据封装在一个类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、本博客的目的是知识交流所用,转载自其它博客或网站,作为自己的参考资料的,感谢这些文章的原创人员

上一篇:HDU3062(2-SAT)


下一篇:理解JavaScript中的“this”