使用JDBC进行简单的增删改查

JDBC为java的基础。用jdbc实现对数据库的增删改查的功能是程序员的基本要求。本例以mysql为例，首先要使用本例需要添加mysql-connector-java-5.1.7-bin.jar包。专门用来加载jdbc的驱动。如果数据库为oracle，相应的jar包换为ojdbc6.jar。

通过下面的代码可以练习一下，掌握jdbc的使用方法，自己可以对程序进行相应的扩展，可以试试oracle数据库，也可以试试MongoDB，还可以试试redis等。

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/jdbc";
        String userName = "root";
        String password = "xiangpeng";//密码

        // 获取数据库连接
        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;
         }

}

表结构的数据类型如下：

 

上面获取object的值的方法，这个type为java.sql.Types的具体对应值，具体的数值见下面：

//java.sql.Types具体值
public final static int BIT   =  -7; 
public final static int TINYINT  =  -6; 
public final static int SMALLINT =   5; 
public final static int INTEGER  =   4; 
public final static int BIGINT   =  -5; 
public final static int FLOAT   =   6; 
public final static int REAL   =   7; 
public final static int DOUBLE   =   8; 
public final static int NUMERIC  =   2; 
public final static int DECIMAL  =   3; 
public final static int CHAR  =   1; 
public final static int VARCHAR  =  12; 
public final static int LONGVARCHAR  =  -1; 
public final static int DATE   =  91; 
public final static int TIME   =  92; 
public final static int TIMESTAMP  =  93; 
public final static int BINARY  =  -2; 
public final static int VARBINARY  =  -3; 
public final static int LONGVARBINARY  =  -4; 
public final static int NULL  =   0; 
public final static int OTHER  = 1111; 
public final static int JAVA_OBJECT   = 2000; 
public final static int DISTINCT  = 2001; 
public final static int STRUCT  = 2002; 
public final static int ARRAY   = 2003; 
public final static int BLOB   = 2004; 
public final static int CLOB   = 2005; 
public final static int REF     = 2006; 
public final static int DATALINK = 70; 
public final static int BOOLEAN = 16; 
public final static int ROWID = -8; 
public static final int NCHAR = -15; 
public static final int NVARCHAR = -9; 
public static final int LONGNVARCHAR = -16; 
public static final int NCLOB = 2011; 
public static final int SQLXML = 2009; 
[color=blue][/color][size=large][/size]