JDBC_create.rar_JDBC crea_access jdbc

/** * ClassName:JdbcCreate * Description: file:D:|jdbc\JdbcCreate.java 创建和删除表综合示例 * Copyright: Copyright (c) 2002 * Company: * @author:Liyunshan * @version 1.0 */ //注意：程序运行时，需要在命令行中输入欲创建表的名字。 import java.sql.*; public class JdbcCreate { public static void main(String args[]) { Connection con; //数据库的连接 Statement stmt; //代表一个发送到数据库来执行的数据库命令 PreparedStatement pstmt;//执行预编译的SQL语句 ResultSet rs; //读取的数据结果，也就是数据库返回的结果 //包含了被访问数据库或者数据源的名称，用URL形式表示 . String source = "jdbc:odbc:management"; String user = ""; //数据库的用户名 String password = ""; //数据库的密码 String sql = ""; //读取命令行中的表名，如果没有，则提示退出 String tableName = ""; if (args.length > 0) { tableName = args[0].trim(); //去掉多余的空格 } else { System.out.println("请在命令行中输入欲创建的数据库表名!"); System.exit(0); } try { //打开数据库的连接 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); con = DriverManager.getConnection(source,user,password); stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); //1---开始创建表操作 //首先，检查如果同名表已经存在，则先删除同名表 try { sql = "DROP TABLE " + tableName; stmt.executeUpdate(sql); } catch(Exception ex) { System.out.println("下面进行" + tableName + "表的创建!"); } //创建表,四个属性列：编号id，姓名name，年龄age，籍贯home sql = "CREATE TABLE " + tableName + "(id INT NOT NULL UNIQUE," + "name CHAR(10) NOT NULL,age INT,home CHAR(10))"; stmt.executeUpdate(sql); //增加属性列:职称title sql = "ALTER TABLE " + tableName + " ADD title CHAR(15)"; stmt.executeUpdate(sql); System.out.println(tableName + "表已成功创建!"); //2---向表中插入数据 /* 执行成批更新，可以提高程序的性能。 一个成批更新命令收集一连串命令并一次同时提交，执行的效率高。*/ //保存并关闭原来的自动提交模式 try { boolean autoCommit = con.getAutoCommit(); con.setAutoCommit(false); //添加批处理命令:addBatch(sql)方法 sql = "insert into " + tableName + " values(1,'李五',36,'江西','教授')"; stmt.addBatch(sql); sql = "insert into " + tableName + " values(2,'王宾',26,'山东','讲师')"; stmt.addBatch(sql); sql = "insert into " + tableName + " values(3,'林永',48,' 四川','教授')"; stmt.addBatch(sql); sql = "insert into " + tableName + " values(4,'齐刚',26,'上海','助教')"; stmt.addBatch(sql); sql = "insert into " + tableName + " values(5,'刘晓',25,'北京','讲师')"; stmt.addBatch(sql); stmt.executeBatch(); //提交这个成批更新 con.commit(); //把批处理命令作为一个事务提交 con.setAutoCommit(autoCommit); //恢复到原来的自动提交模式 } catch(SQLException sx) { System.out.println("成批更新发生错误，恢复到执行更新之前的错误!"); //取消事务，上一次执行命令后的所有未执行批处理命令都会无效 con.rollback(); } //3---表的查询输出 System.out.println("插入数据后得到的" + tableName + "表为:"); sql = "SELECT * FROM " + tableName + " ORDER BY id"; rs = stmt.executeQuery(sql); while (rs.next()) { //此处用列属性名做参数 int id = rs.getInt("id"); String name = rs.getString("name"); int age = rs.getInt("age"); String home = rs.getString("home"); String title = rs.getString("title"); System.out.println("编号：" + id + ",姓名：" + name + ",年龄：" + age + ",籍贯：" + home + ",职称：" + title); } //4---执行预编译的SQL语句,更新职称 sql = "Update " + tableName + " SET title=? WHERE id=?"; pstmt = con.prepareStatement(sql); //设置"?"代表的值，预编译语句将会接受取代的参数 pstmt.setString(1, "副教授"); pstmt.setInt(2, 4); //第二个"?"代替为4 pstmt.executeUpdate(); System.out.println("编号为2教师的职称更改成功!"); pstmt.setString(1, "助教"); pstmt.setInt(2, 5); pstmt.executeUpdate(); System.out.println("编号为5教师的职称更改成功!"); //5---select语句 ---显示更新后的结果 sql = "SELECT * from " + tableName + " WHERE id=4 or id=5"; System.out.println("更新职称的教师为:"); rs = stmt.executeQuery(sql); while (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); String title = rs.getString("title"); System.out.println("编号:" + id + ",姓名: " + name + ",职称: " + title); } pstmt.close(); stmt.close(); con.close (); } //错误处理 catch(SQLException s) { System.out.println("SQL Error: " + s.toString() + s.getErrorCode() + " " + s.getSQLState()); } catch(Exception e) { System.out.println("Error: " + e.toString() + e.getMessage()); } } }