使用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]

巴特西

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

最新文章

热门文章