一、JDBC 由来和定义
Java 数据库连接，（Java Database Connectivity，简称 JDBC）是 Java 语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。
二、JDBC 的使用
package com.google.study.jdbc;
import com.mysql.jdbc.*;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcDemo {
public static final String URL = "jdbc:mysql://localhost:3306/student";
public static final String USER = "root";
public static final String PASSWORD = "xiaoshi";
public static void main(String[] args) throws SQLException, ClassNotFoundException {
// 1.加载驱动程序「给布料厂商打电话，说明自己是mysql公司，需要布料」
Class.forName("com.mysql.jdbc.Driver");
// 2.获得数据库连接「告诉送货员，具体走什么路线送货」
Connection connection = (Connection) DriverManager.getConnection(URL, USER, PASSWORD);
// 3.获取数据库操作对象「声明货物送到了，开始卸货」
Statement statement = (Statement) connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * FROM info");
while (resultSet.next()) {
int id = resultSet.getInt(2);
String name = resultSet.getString(1);
int age = resultSet.getInt(3);
System.out.println("id:" + id + ",name:" + name + ",age:" + age);
}
// 4.关闭数据库连接
statement.close();
connection.close();
}
}
三、代码规范
package com.google.study.jdbc;
import com.mysql.jdbc.*;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcDemo {
public static final String URL = "jdbc:mysql://localhost:3306/student";
public static final String USER = "root";
public static final String PASSWORD = "xiaoshi";
public static Connection connection;
public static Statement statement;
public static ResultSet resultSet;
public static void main(String[] args) throws SQLException, ClassNotFoundException {
try {
// 1.加载驱动程序「给布料厂商打电话，说明自己是mysql公司，需要布料」
Class.forName("com.mysql.jdbc.Driver");
// 2.获得数据库连接「告诉送货员，具体走什么路线送货」
connection = (Connection) DriverManager.getConnection(URL, USER, PASSWORD);
// 3.获取数据库操作对象「声明货物送到了，开始卸货」
statement = (Statement) connection.createStatement();
resultSet = statement.executeQuery("SELECT * FROM info");
while (resultSet.next()) {
int id = resultSet.getInt(2);
String name = resultSet.getString(1);
int age = resultSet.getInt(3);
System.out.println("id:" + id + ",name:" + name + ",age:" + age);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try{
statement.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
四、封装 JDBCUtils
新建配置文件 db.properties
url=jdbc:mysql://localhost:3306/student
user=root
password=xiaoshi
driver=com.jdbc.mysql.Driver
新建 JDBC 封装类 JDBCUtils
package com.google.study.jdbc;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
private static String driver;
private static String url;
private static String user;
private static String password;
// 静态代码块，预先读取配置文件的配置项，做预处理
static {
try {
// 获取当前类加载器
ClassLoader classLoader=JDBCUtils.class.getClassLoader();
// 通过当前累加载器方法获得 文件db.properties的一个输入流
InputStream inputStream = classLoader.getSystemResourceAsStream("db.properties");
//创建一个Properties 对象
Properties properties = new Properties();
// 读取配置文件
properties.load(inputStream);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
} catch (Exception e) {
e.printStackTrace();
}
}
// 连接方法
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
// 关闭数据库连接
public static void close(Connection connection, Statement statement) throws SQLException {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
}
public static void close(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
if (resultSet != null) {
resultSet.close();
}
}
}
测试封装后的 JDBCUtils 类中的功能
创建 JdbcDemo 类对数据库进行增删改
package com.google.study.jdbc;
import com.sun.xml.internal.bind.v2.TODO;
import org.junit.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcDemo {
public Connection connection;
public Statement statement;
public ResultSet resultSet;
@Test
public void insertTest () throws SQLException {
try {
connection = JDBCUtils.getConnection();
statement = connection.createStatement();
String sqlIns = "INSERT INTO info(name,age) VALUES('insert',18)";
String sqlDel = "DELETE FROM info WHERE id=4";
String sqlUpd = "UPDATE info SET name='AA' WHERE id=2";
// 增
int resIns = statement.executeUpdate(sqlIns);
// 删
int resDel = statement.executeUpdate(sqlDel);
// 改
int resUpd = statement.executeUpdate(sqlUpd);
if (resIns > 0) {
System.out.println("Insert success!");
}
if (resDel > 0) {
System.out.println("Delete success!");
}
if (resUpd > 0) {
System.out.println("Update success!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(connection,statement);
}
}
}
五、字符编码问题
上述封装类中插入“中文，符号”等会遇到乱码问题
- 更新 db.prooerties 配置文件：
url=jdbc:mysql://localhost:3306/student?characterEncoding=utf8
user=root
password=xiaoshi
driver=com.jdbc.mysql.Driver
六、管理系统
- 封装 JDBCUtils 数据库配置类
package com.google.study.student;
import com.google.study.jdbc.JDBCUtils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUnitl {
private static String driver;
private static String url;
private static String user;
private static String password;
static {
try {
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
InputStream inputStream = classLoader.getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
driver = properties.getProperty("dricer");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
public static void close (Connection connection, Statement statement) throws SQLException {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
}
public static void close (Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
if (resultSet != null) {
resultSet.close();
}
}
}
- 封装 Student 学生信息类
package com.google.study.student;
import java.rmi.StubNotFoundException;
public class Student {
public static int id;
public static String name;
public static int age;
public Student() {
}
public Student(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}
public void setId(int id) {
this.id = id;
}
public void setAge(int age) {
this.age = age;
}
public void setName(String name) {
this.name = name;
}
public String getName() {
return this.name;
}
public int getId () {
return this.id;
}
public int getAge() {
return this.age;
}
public String toString() {
return "id:" + this.id + "|" + "name:" + this.name + "|" + "age:" + this.age;
}
}
- 封装 StudentServer 数据库操作类
package com.google.study.student;
import com.google.study.jdbc.JDBCUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.Scanner;
public class StudentServer {
public static Connection connection;
public static PreparedStatement preparedStatement;
public static ResultSet resultSet;
public static Scanner scanner = new Scanner(System.in);
public static ArrayList<Student> selectAll() throws SQLException {
ArrayList<Student> arrayList = new ArrayList<>();
try {
connection = JdbcUnitl.getConnection();
String sql = "SELECT * FROM info";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
arrayList.add(new Student(resultSet.getInt(1), resultSet.getString(2), resultSet.getInt(3)));
}
return arrayList;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.close(connection, preparedStatement);
scanner.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return arrayList;
}
public static Student selectById(int id) {
Student student = new Student();
try {
connection = JdbcUnitl.getConnection();
String sql = "SELECT * FROM info WHERE id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
student.setId(resultSet.getInt(1));
student.setName(resultSet.getString(2));
student.setAge(resultSet.getInt(3));
}
return student;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.close(connection, preparedStatement);
scanner.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return student;
}
public static boolean update(int id, String name, int age) {
try {
connection = JdbcUnitl.getConnection();
String sql = "UPDATE info SET name=?,age=? WHERE id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(3,id);
preparedStatement.setString(1,name);
preparedStatement.setInt(2,age);
int resultSet = preparedStatement.executeUpdate();
if (resultSet > 0)
return true;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.close(connection, preparedStatement);
scanner.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return false;
}
public static boolean delete(int id) {
try {
connection = JdbcUnitl.getConnection();
String sql = "DELETE FROM info WHERE id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
int resultSet = preparedStatement.executeUpdate();
if (resultSet > 0)
return true;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.close(connection, preparedStatement);
scanner.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return false;
}
public static boolean add(String name,int age) {
try {
connection = JdbcUnitl.getConnection();
String sql = "INSERT INTO info(name,age) VALUES(?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,name);
preparedStatement.setInt(2,age);
int resultSet = preparedStatement.executeUpdate();
if (resultSet > 0)
return true;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.close(connection, preparedStatement);
scanner.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return false;
}
}
- 封装 RunApplication 程序启动类
package com.google.study.student;
import java.sql.SQLException;
import java.util.Scanner;
public class RunApplication {
static Scanner sc = new Scanner(System.in);
public static void main(String[] args) throws SQLException {
init();
}
public static void init() throws SQLException {
System.out.println("-----欢迎进入信息管理系统-----");
System.out.println(
"----------1.selectAll------\n" +
"----------2.selectById-----\n" +
"----------3.update---------\n" +
"----------4.delete---------\n" +
"----------5.add------------\n" +
"----------6.quit-----------");
System.out.println("-----选择序号进行系统操作-----");
functionService();
}
public static void functionService() throws SQLException {
String usingNext = sc.next();
switch (usingNext) {
case "1":
System.out.println(StudentServer.selectAll());
break;
case "2":
System.out.println("请输入要查询学生的id：");
System.out.println(StudentServer.selectById(sc.nextInt()));
break;
case "3":
System.out.println("请输入要更新学生的id，name，age：");
System.out.println(StudentServer.update(sc.nextInt(), sc.next(), sc.nextInt()));
break;
case "4":
System.out.println("请输入要删除学生的id：");
System.out.println(StudentServer.delete(sc.nextInt()));
break;
case "5":
System.out.println("请输入要添加学生的姓名，年龄：");
System.out.println(StudentServer.add(sc.next(), sc.nextInt()));
break;
case "6":
System.out.println("系统退出成功");
System.exit(0);
break;
default:
break;
}
}
}
扫一扫
3833
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
