JDBC:Java 数据库连接,(Java Database Connectivity,简称JDBC)是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法。
在通过JDBC连接数据库时,需加入包:mysql-connector-java-5.1.46.jar 这个jar包,没有加入这个包的话,会报一个:
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver 异常
一般把数据库信息写入在配置文件中config.properties,文件内容如下所示:
driver=com.mysql.jdbc.Driver
url=jdbc\:mysql\://localhost\:3306/test?useSSL\=false
username=root
password=password
一个获得JDBC连接的自定义类如下:
package core;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.PropertyResourceBundle;
public class DataBaseConnection {
// 配置资源文件
private PropertyResourceBundle bundle;
// JDBC驱动
private String jdbcDriver = "";
// 数据库 url
private String url = "";
// 数据库用户名
private String username = "";
// 数据库密码
private String password = "";
/**
* 得到一个数据库连接
*
* @return 一个数据库连接对象
*/
public Connection getConnection() throws IOException {
Connection connection = null;
bundle = new PropertyResourceBundle(
DataBaseConnection.class
.getResourceAsStream("config.properties"));
this.url = getString("url");
this.username = getString("username");
this.password = getString("password");
this.jdbcDriver = getString("driver");
try {
try {
Class.forName(jdbcDriver);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
connection = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
/**
* 读取配置文件中的值
*
* @param key 配置文件的key
* @return key对应的值
*/
private String getString(String key) {
return this.bundle.getString(key);
}
}
insert操作:
private static void insert(long number) {
DataBaseConnection con=new DataBaseConnection();
Connection conn = con.getConnection();
String sql = "insert into students (Name,Sex,Age) values(?,?,?)";//预编译语句
PreparedStatement pstmt;
try {
conn.setAutoCommit(false);//不自动提交
pstmt = conn.prepareStatement(sql);
for(int i=0;i<number;i++){
pstmt.setInt(1, i);
pstmt.setString(2, name+i);
pstmt.setString(3, age+i);
statement.addBatch();
if((i+1)%20000==0){//满20000条执行提交
statement.executeBatch();
connection.commit();//提交
//清空已有的sql
statement.clearBatch();
}
}
} catch (Exception e) {
e.printStackTrace();
}
finally{
try{
if(pstmt!=null) pstmt.close();
if(conn!=null) conn.close();
}
catch(Exception){}
e.printStackTrace;
}
}
update操作:
private static int update(Student student) {
Connection conn = new DataBaseConnection().getConnection();
int i = 0;
String sql = "update user set age=3 where id=2";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("resutl: " + i);
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
select操作:
private static Integer getAll() {
Connection conn = getConn();
String sql = "select * from students";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement)conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
int col = rs.getMetaData().getColumnCount();//字段列数
while (rs.next()) {
for (int i = 1; i <= col; i++) {
System.out.print(rs.getString(i) + "\t");
//String age=rs.getString("age"); 得到字段名为age的rs当前值
}
}
System.out.println("");
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
delete操作:
private static int delete(String name) {
Connection conn = new DataBaseConnection().getConnection();
int i = 0;
String sql = "delete from students where id>5";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("resutl: " + i);
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}