目录
前言
一、对表中数据的增删改查。
二、批量插入数据
三、对数据库日期类型的插入与查询
四、clob、blob的操作
1、clob
2、blob
前言
记录Java对mysql数据库进行常见操作。
1、对表中数据的增删改查。
2、批量插入数据。
3、对数据库日期类型的插入与查询
4、clob、blob的操作。
一、对表中数据的增删改查。
首先准备好user表创建如下：
creat table user(
id int,
name varchar(10),
age int,
gender char(1)
)
表中有字段：id name age gender。
对表实现增删改查操作，代码如下：
package JDBCTest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class JDBC {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
List list = new ArrayList();
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xixi","root","0705");
// 增加一条数据
String sql1 ="INSERT INTO user VALUES(?,?,?,?)";
PreparedStatement pstmt1 = con.prepareStatement(sql1);
pstmt1.setInt(1, 2);
pstmt1.setString(2, "ls");
pstmt1.setInt(3, 18);
pstmt1.setString(4, "女");
int count = 0;
count = pstmt1.executeUpdate();
System.out.println("成功增加"+count+"条数据!");
pstmt1.close();
//删除一条数据
String sql2 ="DELETE FROM user WHERe id=?";
PreparedStatement ps2 = con.prepareStatement(sql2);
ps2.setInt(1, 2);
int count2 =0;
count2 = ps2.executeUpdate();
System.out.println("删除"+count2+"条记录");
//修改数据
String sql3 ="UPDATE user SET NAME='xiaohu' WHERe id=?";
PreparedStatement ps3 = con.prepareStatement(sql3);
ps3.setInt(1, 1);
int count3 =0;
count3 = ps3.executeUpdate();
System.out.println("修改"+count3+"条记录");
//查询数据
String sql4 = "SELECT * FROM user WHERe id=?";
PreparedStatement ps4 = con.prepareStatement(sql4);
ps4.setInt(1, 3);
ResultSet rs = ps4.executeQuery();
while(rs.next()) {
User user = new User();
user.setAge(rs.getInt(3));
user.setName(rs.getString(2));
user.setId(rs.getInt(1));
user.setGender(rs.getString(4));
list.add(user);
}
for(User user:list) {
System.out.println(user.getId()+"--"+user.getName()+"--"+user.getAge()+"--"+user.getGender());
}
}
}
加载驱动
建立连接
使用PreparedStatement预加载，可以防止sql注入
执行executeUpdate（）和executeQuery（）方法
二、批量插入数据
package JDBCTest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class TestBath {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver"); //连接驱动
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xixi","root","0705"); //建立连接
con.setAutoCommit(false); //手动提交
String sql;
Statement stmt = con.createStatement();
for(int i=0;i<20;i++) {
int a = (int) Math.round(Math.random()*100);
sql="insert into user(name,age) values('周"+i+"',"+a+")";
stmt.addBatch(sql);
}
int a[] = stmt.executeBatch();
System.out.println(a.length+"条记录生成成功！");
con.commit();
stmt.close();
con.close();
}
}
package JDBCTest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class TestBath {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver"); //连接驱动
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xixi","root","0705"); //建立连接
con.setAutoCommit(false); //手动提交
String sql;
Statement stmt = con.createStatement();
for(int i=0;i<20;i++) {
int a = (int) Math.round(Math.random()*100);
sql="insert into user(name,age) values('周"+i+"',"+a+")";
stmt.addBatch(sql);
}
int a[] = stmt.executeBatch();
System.out.println(a.length+"条记录生成成功！");
con.commit();
stmt.close();
con.close();
}
}
加载驱动
建立连接
设置手动提交 ——con.setAutoCommit(False)
注意，使用Statement而不是PreparedStatement，后者有时候会出错（有了解原因的小伙伴欢迎评论区解答）。
add.bath
最后在手动提交
三、对数据库日期类型的插入与查询
package JDBCTest;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestDate {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver"); //连接驱动
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xixi","root","0705"); //建立连接
//查询
String sql1 = "select * from test_date";
PreparedStatement ps = con.prepareStatement(sql1);
ResultSet rs =ps.executeQuery();
while(rs.next()) {
System.out.println(rs.getInt(1)+"--"+rs.getString(2)+"--"+rs.getDate(3));
//修改 1号员工的入职日期
String sql2 = "update test_date set interTime=? where id =1";
PreparedStatement ps2 =con.prepareStatement(sql2);
Date d = new Date(System.currentTimeMillis());
ps2.setDate(1, d);
ps2.executeUpdate();
}
}
}
与普通的增删改查差不多
需注意对应Java里的Date为import java.sql.Date包
四、clob、blob的操作
1、clob
package JDBCTest;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestClob {
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
Class.forName("com.mysql.cj.jdbc.Driver"); //连接驱动
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xixi","root","0705"); //建立连接
//存入b.txt
String sql1="insert into text(txt) values(?)";
PreparedStatement ps1 = con.prepareStatement(sql1);
Reader rd = new FileReader("b.txt");
ps1.setCharacterStream(1, rd);
ps1.executeUpdate();
//读取
String sql2 ="select txt from text where id=?";
PreparedStatement ps2 = con.prepareStatement(sql2);
ps2.setInt(1, 1);
Writer wt = new FileWriter("aaaaa.txt");
Reader rd2 =null;
char temp[] = new char[1024*10];
int len =-1;
// int temp =0;
ResultSet rs =ps2.executeQuery();
while(rs.next()) {
rd2 =rs.getCharacterStream(1);
while((len=rd2.read(temp))!=-1) {
wt.write(temp,0,len);
}
}
wt.flush();
wt.close();
rd2.close();
rd.close();
ps2.close();
ps1.close();
con.close();
}
}
2、blob
package JDBCTest;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestBlob {
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
Class.forName("com.mysql.cj.jdbc.Driver"); //连接驱动
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xixi","root","0705"); //建立连接
InputStream is2 =null;
OutputStream os = null ;
//添加
String sql1 = "insert into movie(mov) values(?)";
PreparedStatement ps1 = con.prepareStatement(sql1);
InputStream is = new FileInputStream("009.jpg");
ps1.setBinaryStream(1, is);
ps1.executeUpdate();
//查询
String sql2 ="select mov from movie where id=?";
PreparedStatement ps2 = con.prepareStatement(sql2);
ps2.setInt(1, 1);
ResultSet rs = ps2.executeQuery();
while(rs.next()) {
os = new FileOutputStream("jdbcCope.jpg");
is2 = rs.getBinaryStream(1);
byte temp[] = new byte[1024];
while(is2.read(temp)!=-1) {
os.write(temp, 0, temp.length);
}
}
is.close();
os.flush();
os.close();
is2.close();
ps2.close();
ps1.close();
con.close();
}
}
package JDBCTest;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestClob {
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
Class.forName("com.mysql.cj.jdbc.Driver"); //连接驱动
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xixi","root","0705"); //建立连接
//存入b.txt
String sql1="insert into text(txt) values(?)";
PreparedStatement ps1 = con.prepareStatement(sql1);
Reader rd = new FileReader("b.txt");
ps1.setCharacterStream(1, rd);
ps1.executeUpdate();
//读取
String sql2 ="select txt from text where id=?";
PreparedStatement ps2 = con.prepareStatement(sql2);
ps2.setInt(1, 1);
Writer wt = new FileWriter("aaaaa.txt");
Reader rd2 =null;
char temp[] = new char[1024*10];
int len =-1;
// int temp =0;
ResultSet rs =ps2.executeQuery();
while(rs.next()) {
rd2 =rs.getCharacterStream(1);
while((len=rd2.read(temp))!=-1) {
wt.write(temp,0,len);
}
}
wt.flush();
wt.close();
rd2.close();
rd.close();
ps2.close();
ps1.close();
con.close();
}
}
2、blob
package JDBCTest;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestBlob {
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
Class.forName("com.mysql.cj.jdbc.Driver"); //连接驱动
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xixi","root","0705"); //建立连接
InputStream is2 =null;
OutputStream os = null ;
//添加
String sql1 = "insert into movie(mov) values(?)";
PreparedStatement ps1 = con.prepareStatement(sql1);
InputStream is = new FileInputStream("009.jpg");
ps1.setBinaryStream(1, is);
ps1.executeUpdate();
//查询
String sql2 ="select mov from movie where id=?";
PreparedStatement ps2 = con.prepareStatement(sql2);
ps2.setInt(1, 1);
ResultSet rs = ps2.executeQuery();
while(rs.next()) {
os = new FileOutputStream("jdbcCope.jpg");
is2 = rs.getBinaryStream(1);
byte temp[] = new byte[1024];
while(is2.read(temp)!=-1) {
os.write(temp, 0, temp.length);
}
}
is.close();
os.flush();
os.close();
is2.close();
ps2.close();
ps1.close();
con.close();
}
}
blob存储可以操作二进制文件，clob只能文本文件。
和普通的区别在于是操作的io流而不是直接操作数据。
