摘要：
使用jdbc实现数据库的添加、删除、修改和查询功能是程序员的基本要求。本例以MySQL为例。要使用此示例，需要添加mysql-connector-java-5.1.7-bin-Jar包。专门用于加载jdbc的驱动程序。以下代码可用于练习和掌握jdbc的使用。您可以自己扩展程序，例如oracle数据库、MongoDB和Redis。packagejdbc；导入java.sql.*；导入java.util。UUID；publicclassJDBC_测试｛publicstaticConnectingetConnection（）throwsException｛Stringdriver_mysql=“com.mysql.jdbc.Driver”；Class.forName；//数据库连接字符串Stringmysql_url=“jdbc:mysql://127.0.0.1:3306/jdbc“；StringuserName=”root“；String Password=”xingpeng“；//Password//获取数据库连接Connectioncann=DriverManager.getConnection；returnconn；｝publicstatic void mainthrowsException｛//调用以下方法//JDBC_Test.createTableTest（）；//创建表//JDBC_Test.addTest（;//批量添加JDBC_Test。查询测试（）//查询//JDBC_Test。更新测试（）；//更新//JDBC_测试。delTest（）；//Delete｝//创建表publicstaticvoidcreateTableTest（）throwsException｛Connectioncann=getConnection（）；Stringsql=“createtableJDBC_STUDENT3”；PreparedStatementprestmt=conn.prepareStatement；boolean=prestmt.execute（）；System.out.println；prestmt.close（）；conn.close（））；｝//添加publicstaticvoidadTest（）throwsException｛Connectioncann=getConnection（）；Statementstmt=conn.createState（）；Stringsql=“insertintojdbc_student”+“values”；Stringuuid=getUUID（）；Stringsql_uuid=“insert intojbc_studen”+“values”；intresult=stmt.executeUpdate；System.out.println；stmt.close（）；conn.close（））；｝//Java注入，使用预编译方法插入数据publicstaticvoidaddTest1（）throwsException｛Connectioncann=getConnection（）；conn.setAutoCommit；Stringsql=“insertintojdbc_student”+“values（？
JDBC为java的基础。用jdbc实现对数据库的增删改查的功能是程序员的基本要求。本例以mysql为例，首先要使用本例需要添加mysql-connector-java-5.1.7-bin.jar包。专门用来加载jdbc的驱动。如果数据库为oracle，相应的jar包换为ojdbc6.jar。
通过下面的代码可以练习一下，掌握jdbc的使用方法，自己可以对程序进行相应的扩展，可以试试oracle数据库，也可以试试MongoDB，还可以试试redis等。
packagejdbc; import java.sql.*; importjava.util.UUID; public classJDBC_Test { public static Connection getConnection() throwsException { 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); returnconn; } public static void main(String[] args) throwsException { //调用下面的方法 //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()throwsException{ 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() throwsException{ 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() throwsException { 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 = newjava.util.Date(); java.sql.Timestamp time = newjava.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()throwsException{ 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() throwsException { 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()throwsException{ 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()throwsException{ 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 staticString getUUID() { return UUID.randomUUID().toString().replace("-", ""); } //获取object的值 public static Object getValue(ResultSet rs, int type, String columnName)throwsSQLException { //type为java.sql.Types的具体对应值 if (type == 4) {//integer类型 returnrs.getInt(columnName); } else if (type == 12) {//varchar2类型 returnrs.getString(columnName); } else if (type == 91) {//date类型 returnrs.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]