JDBC的操作步骤
1.注册驱动
2.获得连接
3.定义SQL语句
4.获取执行SQL的对象
5.执行SQL语句
6.处理执行后的结果
7.释放资源
下面是详细的代码演示及优化
(1)定义一个model类,Person,添加get,set,及构造方法
package com.hanqi.jdbc;
public class Person {
private int id ;
private String name;
private String sex;
private String info;
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 String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getInfo() {
return info;
}
public void setInfo(String info) {
this.info = info;
}
public Person() {
super();
}
public Person(int id, String name, String sex, String info) {
this.id = id;
this.name = name;
this.sex = sex;
this.info = info;
}
@Override
public String toString() {
return "Person{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", info='" + info + '\'' +
'}';
}
}
(2)定义一个jdbc工具类JdbcUtils
package com.hanqi.jdbc;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.net.URLDecoder;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
/**
* 创建一个jdbc的工具类,简化创建连接和释放资源的操作
*/
private static String url;
private static String user;
private static String password;
//创建静态代码块,在类加载的时候执行一次,用来读取配文件
static {
try {
//创建properties集合类
Properties properties = new Properties();
//注意,用类加载器来动态获取src下的配置文件路径
ClassLoader classLoader = JdbcUtils.class.getClassLoader();
URL resource = classLoader.getResource("jdbc.properties");
String path1 = resource.getPath();
//用urlDecoder来解决中文jdbc中文路径乱码的问题
String path = URLDecoder.decode(path1, "utf-8");
System.out.println(path);
//加载配置文件
// properties.load(new FileReader("src/jdbc.properties"));
//用path来动态的获取路径
properties.load(new FileReader(path));
//获取信息
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
String driver = properties.getProperty("driver");
//注册驱动
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//1.定义创建连接的方法
public static Connection getConnection(){
try {
System.out.println("url"+url);
Connection connection = DriverManager.getConnection(url, user, password) ;
return connection;
}catch (Exception e){
return null;
}
}
//2.定义释放资源的方法
public static void close(Statement statement ,Connection connection){
if (statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//3.释放资源方法重载
public static void close(ResultSet rs ,Statement statement ,Connection connection){
if (rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
(3)定义测试类
package com.hanqi.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
public class jdbcUtilsTest {
//JDBC工具类使用测试
public static void main(String[] args) {
ArrayList<Person> list= new ArrayList<>();
Statement statement=null;
ResultSet rs=null;
//用优化后的jdbc工具箱来执行查询所有信息的sql语句
//获取工具类
JdbcUtils jdbcUtils = new JdbcUtils();
//获得连接
Connection connection = JdbcUtils.getConnection();
System.out.println(connection);
//创建sql
String sql="select * from person";
//创建执行sql的对象
try {
statement = connection.createStatement();
rs = statement.executeQuery(sql);
//接收结果集
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String sex = rs.getString("sex");
String info = rs.getString("info");
list.add(new Person(id,name,sex,info));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//遍历list集合
for (Person person:list) {
System.out.println(person);
}
//释放资源
jdbcUtils.close(rs,statement,connection);
}
}
}
注意
在执行getPath()时由于包含中文路径,会出现乱码问题。
String path1 = resource.getPath();
要用urlDecoder来解决中文路径乱码的问题
String path = URLDecoder.decode(path1, "utf-8");
此时,输出的path是UTF-8编码,解决了乱码问题。