连接数据库，进行单元测试--增删改查

import org.junit.Test;

import java.sql.*;

public class Test1 {
/**
* 测试查询
*/
@Test
public void testQuery(){
//mysql驱动类
String driverClass = “com.mysql.jdbc.Driver”;
String url = “jdbc:mysql://192.168.93.88:3306/mydb”;
String username = “root”;
String password = “”;
//数据库连接接口
Connection conn = null;
//执行sql语句的接口
Statement st = null;
ResultSet rs = null;
//操作数据库返回的查询结果的接口
try {
//加载驱动类到虚拟机
Class.forName ( driverClass );

        //连接数据库
        conn =  DriverManager.getConnection (url,username,password);//url地址，username,password

        //创建语句对象
        st = conn.createStatement (); //功能是执行sql语句

        String sql = " select e.empno,e.ename ename ,e.job,d.dname dname" +
                     " from emp e join dept d " +
                     " where e.deptno = d.deptno and e.job = 'CLERK' ";
        rs = st.executeQuery ( sql );   //拿到查询的结果集

        //操作
        while (rs.next ()){
            //取列的值
            System.out.println (rs.getInt ( 1 ));
            System.out.println (rs.getString ( "ename" ));
            System.out.println (rs.getString ( 3 ));
        }
    } catch (Exception e) {
        e.printStackTrace ();
    }finally {
        //关闭这些对象，是为了释放资源
        try {
            if (st != null)
                st.close ();
            if (conn != null)
                conn.close ();
        } catch (SQLException e) {
            e.printStackTrace ();
        }
    }
}

/**
 * 测试testprepareStatement()，查询
 */
@Test
public void testprepareStatement(){
    //mysql驱动类
    String driverClass = "com.mysql.jdbc.Driver";
    String url = "jdbc:mysql://192.168.93.88:3306/mydb";
    String username = "root";
    String password = "";
    //数据库连接接口
    Connection conn = null;
    //执行sql语句的接口
    PreparedStatement prst = null;
    ResultSet rs = null;
    //操作数据库返回的查询结果的接口
    try {
        //加载驱动类到虚拟机
        Class.forName ( driverClass );

        //连接数据库
        conn =  DriverManager.getConnection (url,username,password);//url地址，username,password

        String sql = "select e.empno,e.ename ename ,e.job,d.dname dname" +
                     " from emp e join dept d" +
                     " where e.deptno = d.deptno and e.job = ?";
        //创建语句对象
        prst = conn.prepareStatement ( sql ); //功能是执行sql语句

        prst.setString ( 1,"CLERK");

        rs = prst.executeQuery ( );
        //拿到查询的结果集 错误语法prst.executeQuery ( sql);应为在准备阶段已经将sql语句给了prepareStatement对象

        //操作
        while (rs.next ()){
            //取列的值
            System.out.println (rs.getString ( 1 ));
            System.out.println (rs.getString ( "ename" ));
            System.out.println (rs.getString ( 3 ));
        }
    } catch (Exception e) {
        e.printStackTrace ();
    }finally {
        //关闭这些对象，是为了释放资源
        try {
            if (prst != null)
                prst.close ();
            if (conn != null)
                conn.close ();
        } catch (SQLException e) {
            e.printStackTrace ();
        }
    }
}

/**
 * 插入
 */
@Test
public void prepare_insert(){
    //mysql驱动类
    String driverClass = "com.mysql.jdbc.Driver";
    String url = "jdbc:mysql://192.168.93.88:3306/mydb";
    String username = "root";
    String password = "";
    //数据库连接接口
    Connection conn = null;
    //执行sql语句的接口
    PreparedStatement prst = null;
    ResultSet rs = null;
    //操作数据库返回的查询结果的接口
    try {
        //加载驱动类到虚拟机
        Class.forName ( driverClass );

        //连接数据库
        conn =  DriverManager.getConnection (url,username,password);//url地址，username,password

        String sql = "insert into student(code,name,sex,phone,enter_time) values(?,?,?,?,?)";
        //创建语句对象
        prst = conn.prepareStatement ( sql ); //功能是执行sql语句

        prst.setInt ( 1,1324);
        prst.setString ( 2,"李四" );
        prst.setString ( 3,"M" );
        prst.setString ( 4,"256562323");
        prst.setDate ( 5,new Date ( new java.util.Date (  ).getTime ()));

        prst.executeUpdate ();

    } catch (Exception e) {
        e.printStackTrace ();
    }finally {
        //关闭这些对象，是为了释放资源
        try {
            if (prst != null)
                prst.close ();
            if (conn != null)
                conn.close ();
        } catch (SQLException e) {
            e.printStackTrace ();
        }
    }
}

@Test
public void prepare_update(){
    //mysql驱动类
    String driverClass = "com.mysql.jdbc.Driver";
    String url = "jdbc:mysql://192.168.93.88:3306/mydb";
    String username = "root";
    String password = "";
    //数据库连接接口
    Connection conn = null;
    //执行sql语句的接口
    PreparedStatement prst = null;
    ResultSet rs = null;
    //操作数据库返回的查询结果的接口
    try {
        //加载驱动类到虚拟机
        Class.forName ( driverClass );

        //连接数据库
        conn =  DriverManager.getConnection (url,username,password);//url地址，username,password

        //删除
        String sql = "delete  from student where code = ? and sex = ? ";
        //创建语句对象
        prst = conn.prepareStatement ( sql ); //功能是执行sql语句

        prst.setInt ( 1,1111);
        prst.setString ( 2,"男" );
        prst.executeUpdate ();

    } catch (Exception e) {
        e.printStackTrace ();
    }finally {
        //关闭这些对象，是为了释放资源
        try {
            if (prst != null)
                prst.close ();
            if (conn != null)
                conn.close ();
        } catch (SQLException e) {
            e.printStackTrace ();
        }
    }
}

}