11.jdbc 操作数据库例子

操作数据库的方式包括CRUD，其中大部分都比较简单，唯一需要关注的可能是获取自增id的用法。

下面我们将以一一举例来说明这些用法。

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.执行存储过程

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 ConnectionTest {

    @Test
    public void execFunction() throws Exception {
        Connection connection = ConnManager.initMysql();
        String result;
        CallableStatement statement = connection.prepareCall("{ call creat_cal_report_data(?,?,?)}");
        statement.setString(1, "name");
        statement.setString(2, "age");
        statement.registerOutParameter(3,java.sql.Types.VARCHAR);
        statement.executeQuery();
        result = (String) statement.getObject(3);
        System.out.println(result);
        statement.close();
        connection.close();
    }
}

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();
}