11.jdbc 操作数据库例子

快来打我* 2021-12-17 12:49 324阅读 0赞

### 文章目录 ###

*  1.获取连接
 *  2.操作数据库
 *   *  1.CRUD例子
     *  2.执行存储过程
     *  3.批量操作例子
     *  4.查询一张表是不是存在

> 操作数据库的方式包括CRUD，其中大部分都比较简单，唯一需要关注的可能是获取自增id的用法。
> 
> 下面我们将以一一举例来说明这些用法。

# 1.获取连接 #

[获取数据库连接参考此文章][Link 1]

# 2.操作数据库 #

## 1.CRUD例子 ##

package com.bsx.test;
    
    import com.bsx.test.db.ConnManager;
    import org.junit.Test;
    
    import java.sql.*;
    
    /** * @Description: * @author: ztd * @date 2019/7/8 下午4:41 */
    public class ConnectionCRUDTest { 
    
        /** * 新增 * @throws Exception */
        @Test
        public void testCreate() throws Exception { 
            Connection connection = ConnManager.initMysql();
            String sql = "insert into test (name,create_time) values (?,?)";
            PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, "p1");
            statement.setTimestamp(2, new Timestamp(new java.util.Date().getTime()));
            statement.executeUpdate();
            ResultSet resultSet = statement.getGeneratedKeys();
            resultSet.next();
            int key = resultSet.getInt(1);
          	// 获取自增id
            System.out.println(key);
            resultSet.close();
            statement.close();
            connection.close();
        }
    
        /** * 删除 * @throws Exception */
        @Test
        public void testDelete() throws Exception { 
            Connection connection = ConnManager.initMysql();
            String sql = "DELETE FROM test WHERE id=?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setLong(1, 6);
            int affectRows = statement.executeUpdate();
            System.out.println(affectRows);
            statement.close();
            connection.close();
        }
    
        /** * 更新 * @throws Exception */
        @Test
        public void testUpdate() throws Exception { 
            Connection connection = ConnManager.initMysql();
            String sql = "update test set name = ? where id=?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, "hehe");
            statement.setLong(2, 1);
            int affectRows = statement.executeUpdate();
            System.out.println(affectRows);
        }
    
        /** * 查询 * @throws Exception */
        @Test
        public void testRetrieve() throws Exception { 
            Connection connection = ConnManager.initMysql();
            String sql = "SELECT * from test where id>?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setLong(1, 1);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) { 
                Long id = resultSet.getLong(1);
                String name = resultSet.getString(2);
                Timestamp createTime = resultSet.getTimestamp(3);
                System.out.println("id: " + id + " name:" + name + " create_time:" + createTime);
            }
            resultSet.close();
            statement.close();
            connection.close();
        }
    }

## 2.执行存储过程 ##

## 3.批量操作例子 ##

package com.dada.test;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.Statement;
    
    public class TestBatch { 
    	public static void main(String[] args) throws Exception { 
    // insertByPreparedStatement();
    		insertByBatchStat();
    // insertByStat();
    	}
    	
    	public static Connection getConnection() { 
    		Connection conn = null;
    		try { 
    			Class.forName("com.mysql.jdbc.Driver");
    			String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf-8";
    			conn= DriverManager.getConnection(url, "root", "test");
    		} catch (Exception e) { 
    			e.printStackTrace();
    		}
    		return conn;
    	}
    	
    	/** * 通过PreparedStatement执行batch * @throws Exception */
    	public static void insertByPreparedStatement() throws Exception { 
    		Connection conn  = getConnection();
    		conn.setAutoCommit(false);
    		String sql = "insert into test(id,name) values(?,?)";
    		PreparedStatement ps = conn.prepareStatement(sql);
    		
    		try{ 
    			for(int i=1;i<10;i++) { 
    // 如果要为某个字段赋予空值，就使用下面的方法，而这个地方也
    // ps.setNull(4,Types.CHAR);
    				ps.setInt(1, i);
    				ps.setString(2, "test"+i);
    				ps.addBatch();
    			}
    			ps.executeBatch();
    			conn.commit();
    		} catch(Exception e) { 
    			try{ 
    				conn.rollback();
    			}catch(Exception e1) { 
    				e1.printStackTrace();
    			}
    			e.printStackTrace();
    		} finally { 
    			try{ 
    				ps.close();
    				conn.close();
    			}catch(Exception e) { 
    				e.printStackTrace();
    			}
    		}
     	}
    	
    	/** * 使用批量插入 * @throws Exception */
    	public static void insertByBatchStat() throws Exception { 
    		Connection conn  = getConnection();
    		conn.setAutoCommit(false);
    		Statement ps = conn.createStatement();
    		
    		try{ 
    			for(int i=1;i<10;i++) { 
    				ps.addBatch("insert into test(id,name) values("+i+",'test"+i+"')");
    			}
    			ps.executeBatch();
    			conn.commit();
    		} catch(Exception e) { 
    			try{ 
    				conn.rollback();
    			}catch(Exception e1) { 
    				e1.printStackTrace();
    			}
    			e.printStackTrace();
    		} finally { 
    			try{ 
    				ps.close();
    				conn.close();
    			}catch(Exception e) { 
    				e.printStackTrace();
    			}
    		}
     	}
    	
    	/** * 直接用Statement插入 * @throws Exception */
    	public static void insertByStat() throws Exception { 
    		Connection conn  = getConnection();
    		conn.setAutoCommit(false);
    		Statement ps = conn.createStatement();
    		
    		try{ 
    			for(int i=1;i<10;i++) { 
    				ps.execute("insert into test(id,name) values("+i+",'test"+i+"')");
    			}
    			ps.executeBatch();
    			conn.commit();
    		} catch(Exception e) { 
    			try{ 
    				conn.rollback();
    			}catch(Exception e1) { 
    				e1.printStackTrace();
    			}
    			e.printStackTrace();
    		} finally { 
    			try{ 
    				ps.close();
    				conn.close();
    			}catch(Exception e) { 
    				e.printStackTrace();
    			}
    		}
     	}
    }

## 4.查询一张表是不是存在 ##

@Test
    public void testTableExists() throws Exception {
        Connection connection = ConnManager.initMysql();
        String sql = "show tables from test like 'test'";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        boolean isTableExist = rs.first();
        System.out.println(isTableExist);
        ps.close();
        connection.close();
    }

[Link 1]: https://blog.csdn.net/tengdazhang770960436/article/details/95078342