最近在项目中添加了用户数据管理功能,使用MFC ODBC连接SQL Server2005数据库。由于以前没有接触过数据库具体的实现,因此花了两周时间学习了数据库的知识,并实现了项目中的基本功能。感觉虽然存在很多问题,但还是有不少收获。下面总结一下,希望能够给跟我
/**
* 数据割接
*/
private String login(HttpServletRequest request,
HttpServletResponse response) {
// userInfoService.add();//数据割接
System.out.println("开始割接===");
// 数据库一:
String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://192.168.1.218:1433;databaseName=DB1";
String user = "sa";
String password = "000000";
Connection conn = null;
// 数据库二:
String driver2 = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url2 = "jdbc:sqlserver://localhost:1433;databaseName=DB2";
String user2 = "sa";
String password2 = "000000";
Connection conn2 = null;
//
try {
Class.forName(driver);// 加载驱动
conn = DriverManager.getConnection(url, user, password);// 打开数据库连接1
Statement stmt = conn.createStatement();
ResultSet rs = stmt
.executeQuery("select RoleId,DeptId,NationId from Users ");
//
Class.forName(driver2);// 加载驱动
conn2 = DriverManager.getConnection(url2, user2, password2);// 打开数据库连接2
PreparedStatement pstmt = conn2
.prepareStatement("insert into Forecast(so2,no2,pm10) values(?,?,?)");
// 循环装入数据
while (rs.next()) {
System.out.println("输出RoleId:" + rs.getInt("RoleId"));
System.out.println("输出DeptId:" + rs.getInt("DeptId"));
System.out.println("输出NationId:" + rs.getInt("NationId"));
pstmt.setDouble(1, rs.getInt("RoleId"));
pstmt.setDouble(2, rs.getInt("DeptId"));
pstmt.setDouble(3, rs.getInt("NationId"));
pstmt.executeUpdate();
}
System.out.println("割接成功");
// 释放资源
rs.close();
stmt.close();
pstmt.close();
conn.close();
conn2.close();
} catch (Exception e) {
System.out.println("割接失败");
e.printStackTrace();
}
return index;
}
/**
* 数据割接2
*/
private String login2(HttpServletRequest request,
HttpServletResponse response) {
/**
* 1.PreparedStatement是预编译的,对于批量处理可以大大提高效率。
* 2.在对数据库只执行一次性存取的时侯,本文转载自:Eclipse连接SQL Server 2008数据库 在连接数据库之前必须保证SQL Server 2008是采用SQL Server身份验证方式而不是windows身份验证方式。如果在安装时选用了后者,则重新设置如下: http://blog.163.com/jackie_howe/blog/static/19949134720122261用 Statement 对象进行处理。
* PreparedStatement 对象的开销比Statement大,对于一次性操作并不会带来额外的好处。
*/
System.out.println("开始割接===");
// 数据库一:
String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://192.168.1.218:1433;databaseName=DB1";
String user = "sa";
String password = "000000";
Connection conn = null;
// 数据库二:
String driver2 = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url2 = "jdbc:sqlserver://localhost:1433;databaseName=DB2";
String user2 = "sa";
String password2 = "000000";
Connection conn2 = null;
//
try {
Class.forName(driver);// 加载驱动
conn = DriverManager.getConnection(url, user, password);// 打开数据库连接1
PreparedStatement stmt = conn.prepareStatement("select RoleId,DeptId,NationId from Users ");
ResultSet rs = stmt.executeQuery();
//
Class.forName(driver2);// 加载驱动
conn2 = DriverManager.getConnection(url2, user2, password2);// 打开数据库连接2
PreparedStatement pstmt = conn2
.prepareStatement("insert into Forecast(so2,no2,pm10) values(?,?,?)");
// 循环装入数据
while (rs.next()) {
System.out.println("输出RoleId:" + rs.getInt("RoleId"));
System.out.println("输出DeptId:" + rs.getInt("DeptId"));
System.out.println("输出NationId:" + rs.getInt("NationId"));
pstmt.setDouble(1, rs.getInt("RoleId"));
pstmt.setDouble(2, rs.getInt("DeptId"));
pstmt.setDouble(3, rs.getInt("NationId"));
pstmt.addBatch();//一个一个加入到批处理命令中
}
int[] count=pstmt.executeBatch();//一把提交给数据库执行
System.out.println("割接成功"+count.length);
// 释放资源
rs.close();
stmt.close();
pstmt.close();
conn.close();
conn2.close();
} catch (Exception e) {
System.out.println("割接失败");
e.printStackTrace();
}
return index;
}