JDBC之增删改查(MYSQL)

package jdbc;

import java.sql.*;
import java.util.UUID;

public class JDBC_Test {
    public static Connection getConnection() throws Exception {
        String driver_mysql = "com.mysql.jdbc.Driver";
        Class.forName(driver_mysql);

        // 数据库连接串
        String mysql_url ="jdbc:mysql://127.0.0.1:3306/test";
        String userName = "username";
        String password = "password";//密码

        // 获取数据库连接
        Connection conn = DriverManager.getConnection(mysql_url, userName, password);
        return conn;
    }

    public static void main(String[] args) throws Exception {
　　//调用下面的方法
        // JDBC_Test.createTableTest(); //建表
        // JDBC_Test.addTest(); //添加
        //JDBC_Test.addTest1(); // 预编译添加
        // JDBC_Test.batchAddTest(); //批量添加
        JDBC_Test.queryTest(); // 查询
        // JDBC_Test.updateTest(); //更新
        // JDBC_Test.delTest(); //删除
    }

         //建表
         public static void createTableTest()throws Exception{
             Connection conn=getConnection();
             String sql="create table JDBC_STUDENT3("+"ID VARCHAR2(32) NOT NULL,"+"NAME VARCHAR2(32),"+"SEX VARCHAR2(32)"+")";
             PreparedStatement prestmt = conn.prepareStatement(sql);
             boolean flag = prestmt.execute();
             System.out.println("执行结果："+flag);
         prestmt.close();
         conn.close();
         }
         
         //添加
         public static void addTest() throws Exception{
             Connection conn=getConnection();
             Statement stmt=conn.createStatement();
             String sql="insert into jdbc_student(id, name, sex, birthday, age)"+"values(seq.nextval,'xp','m','to_date('2009-01-01','yyyy-MM-dd')','24')";
             String uuid=getUUID();
             String sql_uuid="insert into jdbc_student(id, name, sex, birthday, age)"+"values('"+uuid+"','xp','m','to_date('2009-01-01','yyyy-MM-dd')','24')";
             int result=stmt.executeUpdate(sql_uuid);
             System.out.println(result);
         stmt.close();
         conn.close();
         }

        // java注入，采用预编译的方法插入数据
        public static void addTest1() throws Exception {
            Connection conn = getConnection();
            conn.setAutoCommit(false);
            String sql = "insert into jdbc_student(id, name, sex, birthday, age)"+ "values(?,?,?,?,?)";
            PreparedStatement prestmt = conn.prepareStatement(sql);
            String uuid = getUUID();
            prestmt.setString(1, uuid);
            prestmt.setString(2, "xp");
            prestmt.setString(3, "m");
            java.util.Date utilDate = new java.util.Date();
            java.sql.Timestamp time = new java.sql.Timestamp(utilDate.getTime());
            prestmt.setTimestamp(4, time);
            prestmt.setInt(5, 24);
            // 如果第一个结果是resultSet对象，就返回true；如果第一个结果是更新计数或者没有结果，则返回false
            // 意思就是如果是查询的话就返回true，如果是更新或者插入的话就返回false
            boolean result = prestmt.execute();
            System.out.println("是否执行成功：" + result);
            prestmt.close();
            conn.close();
        }    
        
        //批量添加
         public static void batchAddTest()throws Exception{
             Connection conn=getConnection();
             //开辟缓存
             conn.setAutoCommit(false);
             String sqla="insert into jdbc_student(id, name)"+"values('"+getUUID()+"', '张三a')";
             String sqlb="insert into jdbc_student(id, name)"+"values('"+getUUID()+"', '张三b')";
             String sqlc="insert into jdbc_student(id, name)"+"values('"+getUUID()+"', '张三c')";
             Statement stmt =conn.createStatement();
         stmt.addBatch(sqla);
         stmt.addBatch(sqlb);
         stmt.addBatch(sqlc);
         }    
         
         //查询    
         public static void queryTest() throws Exception {
             Connection conn = getConnection();
             String sql = "select * from jdbc_student t where name like ?";
             PreparedStatement prestmt = conn.prepareStatement(sql);
             prestmt.setString(1, "王%");
             ResultSet rs = prestmt.executeQuery();
             ResultSetMetaData rsmd = rs.getMetaData();// 获取元数据
             int columnNum = rsmd.getColumnCount();
             while (rs.next()) {
                 for (int i = 1; i <= columnNum; i++) {
                     System.out.print(rsmd.getColumnName(i) + ": ");
                     System.out.println(JDBC_Test.getValue(rs, rsmd.getColumnType(i), rsmd.getColumnName(i)));
                 }
                 System.out.println("-----------");
             }
             prestmt.close();
             conn.close();
         }
         //更新
         public static void updateTest()throws Exception{
             Connection conn=getConnection();
             Statement stmt=conn.createStatement();
             String sql="update jdbc_student set name='李四散步吧' where id='2'";
             int result=stmt.executeUpdate(sql);
             System.out.println(result);
             stmt.close();
             conn.close();
         }
        //删除
         public static void delTest()throws Exception{
             Connection conn=getConnection();
             Statement stmt=conn.createStatement();
             String sql="delete jdbc_student where id='2'";
             int result=stmt.executeUpdate(sql);
             System.out.println(result);
             stmt.close();
             conn.close();
         }

　　　　  //uuid生成方法
         public static String getUUID() {
             return UUID.randomUUID().toString().replace("-", "");
         }
　　　　　　//获取object的值
         public static Object getValue(ResultSet rs, int type, String columnName)throws SQLException {
             //type为java.sql.Types的具体对应值
             if (type == 4) {// integer类型  
                 return rs.getInt(columnName);
             } else if (type == 12) {// varchar2类型 
                 return rs.getString(columnName);
             } else if (type == 91) {// date类型 
                 return rs.getDate(columnName);
             }
             return null;
         }

}