03-JDBC连接MySQL数据库【插入数据】

灰太狼 2022-06-15 02:36 209阅读 0赞

**JDBC连接mysql–学习目录：**

> 地址：[http://blog.csdn.net/baidu\_37107022/article/details/72600018][http_blog.csdn.net_baidu_37107022_article_details_72600018]

# 1.实现插入步骤 #

![这里写图片描述][SouthEast]

**前三个步骤：注册、获得连接，创建statement对象方法，见上一节：**

**[02-JDBC实战–JDBC查询数据库MySQL][02-JDBC_JDBC_MySQL]–[http://blog.csdn.net/baidu\_37107022/article/details/72597975][http_blog.csdn.net_baidu_37107022_article_details_72597975]**

# 2.使用jdbc向数据库中插入数据 #

**这里使用的是queryDemo数据库，表格为demo1student，表中数据如下：**

![这里写图片描述][SouthEast 1]

## 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.运行前：**  
![这里写图片描述][SouthEast 2]

**2.运行后：**  
**特别说明：id是主键，并且设置了自动增长。因为之前删除了id=9的数据，所以这里从10开始，自动增长的规则见：MySQL【连接地址】**  
![这里写图片描述][SouthEast 3]

--------------------

## 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.运行前**  
![这里写图片描述][SouthEast 4]

**2.运行后：**  
![这里写图片描述][SouthEast 5]

--------------------

## 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.运行前**  
![这里写图片描述][SouthEast 6]

**2.运行后**  
![这里写图片描述][SouthEast 7]

[http_blog.csdn.net_baidu_37107022_article_details_72600018]: http://blog.csdn.net/baidu_37107022/article/details/72600018
[SouthEast]: /images/20220615/09b165505734401b9a2f9738d4203ccf.png
[02-JDBC_JDBC_MySQL]: http://blog.csdn.net/baidu_37107022/article/details/72597975%20jdbc%E6%9F%A5%E8%AF%A2%E6%95%B0%E6%8D%AE%E5%BA%93
[http_blog.csdn.net_baidu_37107022_article_details_72597975]: http://blog.csdn.net/baidu_37107022/article/details/72597975
[SouthEast 1]: /images/20220615/2373179410a84a828dba1fc47ae20ae2.png
[SouthEast 2]: /images/20220615/0e68e46e266a4872a743eaa0350161ce.png
[SouthEast 3]: /images/20220615/c0d7d1b2393e4733832c68f52e41a4de.png
[SouthEast 4]: /images/20220615/79a6b6c6026b4fedb5c745ff0016933c.png
[SouthEast 5]: /images/20220615/742059bef4934c3190180ecbf86b6eec.png
[SouthEast 6]: /images/20220615/74763f205367484bbd968e946e92b0a1.png
[SouthEast 7]: /images/20220615/ec4bdf8fa0f84489bbac230fe54f76b4.png