JDBC的概念:
驱动:连接两个硬件(应用)之间的桥梁,JDBC就是连接java语言和mysql数据库之间的桥梁。
导入驱动jar包
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.25</version>
</dependency>
代码实现:
package com.vrv;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCUtils {
public static String driver;
public static String url;;
public static String user;
public static String password;
static{
driver="com.mysql.jdbc.Driver";
url="jdbc:mysql://localhost:3306/userData";
user="root";
password="123456";
}
/**
* 加载驱动
* @throws ClassNotFoundException
*/
public static void loadDriver() throws ClassNotFoundException{
Class.forName(driver);
}
/**
* 获取连接,可以在获取连接里面一并加载驱动
* @throws ClassNotFoundException
* @throws SQLException
*/
public static Connection getConnection() throws ClassNotFoundException, SQLException{
Connection connection=null;
//加载驱动
loadDriver();
// 获取连接
connection = DriverManager.getConnection(url, user, password);
return connection;
}
/**
* 释放资源
*/
public static void release(ResultSet resultSet,PreparedStatement statement,Connection connection){
//释放resultSet
if (resultSet!=null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
resultSet=null;
}
//释放statement
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
statement=null;
}
//释放connection
if (connection!=null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
connection=null;
}
}
}
package com.vrv;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class JDBCTest {
public static void main(String[] args){
Connection connection=null;
PreparedStatement pstmt=null;
ResultSet resultSet=null;
try {
//获取JDBC连接
connection = JDBCUtils.getConnection();
//1.预编译sql,防止sql注入,新增,修改,删除
pstmt =connection.prepareStatement("insert into user values (?,?)");
pstmt =connection.prepareStatement("update user set name=? where password=?");
pstmt =connection.prepareStatement("delete from user where name=? and password=?");
//设置参数用户名和密码
pstmt.setString(1, "小明");
pstmt.setInt(2, 123456);
//执行sql
int s = pstmt.executeUpdate();
if (s>0) {
System.out.println("成功");
}
//2.查询数据库
pstmt =connection.prepareStatement("select * from user");
resultSet = pstmt.executeQuery();
while (resultSet.next()) {
String name = resultSet.getString("name");
int password = resultSet.getInt("password");
System.out.println(name+","+password);
}
//3.批量插入用户数据
//注意:批量处理在URL上加上批处理参数url="jdbc:mysql://localhost:3306/userData?rewriteBatchedStatements=true"
pstmt =connection.prepareStatement("insert into user values (?,?)");
for (int i = 0; i <=10000; i++) {
pstmt.setString(1, "小明"+i);
pstmt.setInt(2, 123456+i);
//添加到批处理列表
pstmt.addBatch();
//每1000条处理一次
if (i%1000==0) {
//执行批处理
pstmt.executeBatch();
//清空列表
pstmt.clearBatch();
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
//
JDBCUtils.release(resultSet, pstmt, connection);
}
}
}