一直以来项目都是用Hibernate,JDBC反而没有正经练习过,现在将JDBC的基本使用做个小练习。
1. 使用MySQL建立一个test数据库,里面建立一个mytable表,3列(id,name,age);将url, user, password配制成properties文件,放到 工程resource源代码包下面,这里将其命名为db_connect.properties文件
url = jdbc:mysql://localhost:3306/test
user = root
password = admin
2. 将数据库连接封装到一个类中,利用配置文件连接,静态返回connection
package study.jdbc;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DBConnect {
static String url;
static String user;
static String password;
/**
* 获取一个JDBC连接,返回一个Connection对象
* @return connection
*/
public static Connection connectDB() {
Connection connection = null;
readProperties();
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return connection;
}
/**
* 读取properties文件,获取url,user,password
*/
private static void readProperties() {
String fileName = "resouce/db_connect.properties"; //相对于工程
Properties properties = new Properties();
try {
InputStream in = new FileInputStream(fileName);
properties.load(in);
in.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
}
}
3. 对数据库进行增删改查的测试,主要练习使用PreparedStatement
package study.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestMain {
public static void main(String[] args) {
Connection connection = DBConnect.connectDB(); //获取数据库连接
TestMain test = new TestMain();
try { //测试
test.clear(connection);
test.insert(connection); //增
test.query(connection);
System.out.println("----------");
test.delete(connection); //删
test.query(connection);
System.out.println("----------");
test.update(connection); //改
test.query(connection); //查
connection.close(); //关闭数据库连接
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 使用PreparedStatement,效率高
* 动态执行SQL(带参数的SQL语句),是Statement子接口
* 对数据库进行insert,用带参数的语句批量插入
* @param connection
* @throws SQLException
*/
public void insert(Connection connection) throws SQLException {
String sql = "INSERT INTO mytable(id,name,age) values (?,?,22);";
PreparedStatement pr = connection.prepareStatement(sql);
for (int i = 1; i <= 3; i++) {
pr.setInt(1, i);
pr.setString(2, "demo"+i);
pr.executeUpdate();
}
}
/**
* 对数据库进行delete
* @param connection
* @throws SQLException
*/
public void delete(Connection connection) throws SQLException {
String sql = "delete from mytable where id=2;";
PreparedStatement pr = connection.prepareStatement(sql);
pr.executeUpdate();
}
/**
* 对数据库进行update
* @param connection
* @throws SQLException
*/
public void update(Connection connection) throws SQLException {
String sql = "UPDATE mytable SET name='new' WHERE id=1;";
PreparedStatement pr = connection.prepareStatement(sql); // 创建statement对象发送SQL到数据库
pr.executeUpdate(); // 执行UPDATE
}
/**
* 查询query遍历结果集
* @param connection
* @throws SQLException
*/
public void query(Connection connection) throws SQLException {
String sql = "select * from mytable;";
PreparedStatement st = connection.prepareStatement(sql);
ResultSet re = st.executeQuery(); // 查询,返回单个ResultSet对象
while (re.next()) {
int id = re.getInt(1);
String name = re.getString(2);
int age = re.getInt(3);
System.out.println(id + "\t" + name + "\t" + age);
}// 遍历结果集
}
/**
* 清空表
* @param connection
* @throws SQLException
*/
public void clear(Connection connection) throws SQLException {
String sql = "delete from mytable;";
PreparedStatement pr = connection.prepareStatement(sql);
pr.executeUpdate();
}
}