JDBC连接mysql–学习目录:
地址:http://blog.csdn.net/baidu_37107022/article/details/72600018
1.实现插入步骤
前三个步骤:注册、获得连接,创建statement对象方法,见上一节:
02-JDBC实战–JDBC查询数据库MySQL–http://blog.csdn.net/baidu_37107022/article/details/72597975
2.使用jdbc向数据库中插入数据
这里使用的是queryDemo数据库,表格为demo1student,表中数据如下:
1)插入单个数据
SQL语法:insert into 表名 values (值1,值2,值3,…)
代码演示
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import org.junit.Test;
public class Test7 {
//插入insert单个数据
@Test
public void insert1(){
Connection connection=null;
PreparedStatement ps=null;
try {
//1.register
Class.forName("com.mysql.jdbc.Driver");
//2.getConnection
String url="jdbc:mysql://localhost:3306/queryDemo";
Properties info=new Properties();
info.put("user", "root");
info.put("password", "123");
connection=DriverManager.getConnection(url, info);
//3.create Statement
String sql="insert into demo1student (name,age,score) values(?,?,?)";
ps=connection.prepareStatement(sql);
ps.setString(1, "Mary");
ps.setInt(2, 22);
ps.setInt(3, 99);
//4.excuteUpdate
int resultSet=ps.executeUpdate();
if(resultSet>0){
//如果插入成功,则打印success
System.out.println("Sucess");
}else{
//如果插入失败,则打印Failure
System.out.println("Failure");
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//5.关闭资源
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
表格数据变化:
1.运行前:
2.运行后:
特别说明:id是主键,并且设置了自动增长。因为之前删除了id=9的数据,所以这里从10开始,自动增长的规则见:MySQL【连接地址】
2)使用addbatch()–插入多个数据
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.Test;
public class Test8 {
@Test
public void insert5(){
Connection connection=null;
PreparedStatement ps=null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/queryDemo";
String user="root";
String password="123";
connection=DriverManager.getConnection(url, user, password);
String sql="insert into demo1student (name,age,score) values(?,?,?),(?,?,?)";
ps=connection.prepareStatement(sql);
ps.setString(1, "Jane");
ps.setInt(2, 25);
ps.setInt(3, 100);
ps.setString(4, "李磊");
ps.setInt(5, 28);
ps.setInt(6, 99);
//4.excuteUpdate
ps.addBatch();
int[] resultSet=ps.executeBatch();
if(resultSet.length >0){
//如果插入成功,则打印success
System.out.println("Sucess");
}else{
//如果插入失败,则打印Failure
System.out.println("Failure");
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//5.关闭资源
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
表格数据变化:
1.运行前
2.运行后:
3)使用for循环–插入多个数据
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import org.junit.Test;
public class Test9 {
//使用for循环--插入insert多个数据
@Test
public void insert2(){
Connection connection=null;
PreparedStatement ps=null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/queryDemo";
Properties info=new Properties();
info.put("user", "root");
info.put("password", "123");
connection=DriverManager.getConnection(url, info);
String sql="insert into demo1student (name,age,score) values(?,?,?)";
ps=connection.prepareStatement(sql);
int num=0;
for(int i=0;i<5;i++){
//这里我只是随机赋值,可以将你想要添加的数据放在集合中,使用for插入数据
//name赋值
ps.setString(1, "汪洋"+i);
//age赋值
ps.setInt(2, 20+(int)Math.random()*5);
//score赋值
ps.setInt(3, 77+(int)Math.random()*20);
num=ps.executeUpdate();
}
if(num>0){
//如果插入成功,则打印success
System.out.println("Sucess");
}else{
//如果插入失败,则打印Failure
System.out.println("Failure");
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//5.关闭资源
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
运行结果:
1.运行前
2.运行后