/**
* 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());
}
}
}
