使用的IDE:Intellj idea 13.1
数据库中表的增删改查
1、连接SqlServer数据库
<span style="white-space:pre"> </span>static Connection conn;
<span style="white-space:pre"> </span>//static PreparedStatement st;
<span style="white-space:pre"> </span>static java.sql.Statement stat;
String url = "jdbc:sqlserver://EE-LAB; DatabaseName=Fantastic FourMen";
String name = "user";
String passWord = "123456";
//Connection con=null;
//java.sql.Statement stat=null;
//ResultSet rs=null;
try {
//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
System.out.println("驱动程序加载");
System.out.println("即将连接到数据库");
} catch (ClassNotFoundException e) {
System.out.println("无法加载驱动程序" + e.getMessage());
e.printStackTrace();
return;
}
conn = DriverManager.getConnection(url, name, passWord);
conn.close();
public static void insertData(String insertSql) {
try {
//String sql = Format("SET NOCOUNT ON "+ insertSql);
stat = conn.createStatement();
//ResultSet rs = stat.executeQuery(insertSql);
int count = stat.executeUpdate(insertSql); //用这个代替ResultSet rs = stat.executeQuery(insertSql); 同样可以插入
System.out.println("成功插入" + count + "行");
//rs.close();
stat.close();
} catch (SQLException e) {
System.out.println("数据插入失败");
e.printStackTrace();
}
}
3、删除数据
private static void deleteData() {
String deleteSql = "DELETE FROM new_table " + "WHERE ID = 5 ";
try {
stat = conn.createStatement();
int count = stat.executeUpdate(deleteSql);
stat.close();
System.out.println("删除了" + count + "条数据");
} catch (SQLException e) {
System.out.println("数据删除失败");
e.printStackTrace();
}
}
private static void upData() {
String updateSql = "UPDATE testTable SET age = 5 WHERE name ='ling'";
try {
stat = conn.createStatement();
int count = stat.executeUpdate(updateSql);
stat.close();
} catch (SQLException e) {
System.out.println("数据修改失败");
e.printStackTrace();
}
}
5、查询数据
public static void serchData(String serchSql) {
try {
stat = conn.createStatement();
ResultSet rs = stat.executeQuery(serchSql);
System.out.println("查询结果:");
while (rs.next()) {
System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4));
}
rs.close();
stat.close();
} catch (SQLException e) {
System.out.println("查询失败");
e.printStackTrace();
}
}
6、创建新表,并添加另外一个表的数据
private static void createInsertNewTable() {
String serchSql = "select * from new_table";//创建新表
String insertSql = "insert into new_table" + " select name,age,sex " + "from testTable";//将testTable表中的数据插入到新表中
createTable("new_table"); //创建一张新表
insertData(insertSql); //插入数据
serchData(serchSql); //查找数据
}
private static void createTable(String tableName) {
String createTableSql = "create table " + tableName + " (ID int primary key ,name ntext,age int,sex nchar(10))";
//System.out.println(createTableSql);
try {
stat = conn.createStatement();
int count = stat.executeUpdate(createTableSql);
stat.close();
//ResultSet rs = stat.executeQuery(createTableSql);
//rs.close();
} catch (SQLException e) {
System.out.println(tableName + "创建失败");
e.printStackTrace();
}
}