Java通过JDBC对数据库的连接操作其通用操作为:
String username;
String password;
String databaseURL;
Class.forName("JDBCDriverClass");
Connection connection=DriverManager.getConnection(databaseURL,username,password);
MySQL 连接:
Class.forName("com.mysql.jdbc.Driver");
Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/databasename", "root", " ");
SQLServer连接 :
Class.forName('com.microsoft.jdbc.sqlserver.SQLServerDriver');
String url='jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=databasename';
String user='sa';
String password='' ";
Connection conn= DriverManager.getConnection(url,user,password);
Access(通过JDBC-ODBC桥)连接:Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection connection=DriverManager.getConnection("jdbc:odbc:DataSource", "username", "password");
Oracle连接 :
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection=DriverManager.getConnectio("jdbc:oracle:thin:@hostname:port#:oracleDBSID", "0", "0");
创建语句:
Statement statement=connection.createStatement();
执行插入更新等:int count=statement.executeUpdate("create table/insert into/update/delete..."); 返回影响的行数。
执行查询语句:ResultSet resultset=statement.executeQury("select..."); 返回ResultSet。
处理结果(ResultSet的处理):
while(resultset.next()){
...
resultset.getString/getInt/getDouble...(列序号); 如resultset.getString(1),resultset.getDouble(3)... 列序号
从1开始,第一列为1,第二列为2...
resultset.getString/getInt/getDouble...("列名"); 如resultset.getString("SName"),resultset.getInt("S#")...
...
}
批处理:
statement.addBatch("insert into ...");
statement.addBatch("create table...");
statement.addBatch("update...");
...
int count[ ]=statement.executeBatch(); 返回影响行数的数组。
预处理语句:
预处理语句接口PreparedStatement是Statement的接口拓展。
PreparedStatement/Statement pstmt=connection.prepareStatement("insert into student(S#,SName,Age,Sex)"
values(?,?,?,?));
其中?为占位符,占位符位置从1开始,第一个?为第一位,的二个?为第二位...
占位符参数的设置为:pstmt.setX(int index,X value); index为?的索引,X为参数类型如String,int ,double等等。
对上述创建的预处理语句执行如下:
pstmt.setInt(1,0755010100);
pstmt.setString(2,"jack");
pstem.setInt(3,20);
pstmt.setString(4,"man");
int count=pstmt.executeUpdate();
对MySQL的连接例子:package JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MySQLJdbcDemo {
public static void main(String[] args){
MySQLJdbcDemo demo=new MySQLJdbcDemo();
}
private Connection connection;
private Statement statement;
private ResultSet resultset;
private PreparedStatement pstmt;
public MySQLJdbcDemo() {
try {
//数据库的连接
Class.forName("com.mysql.jdbc.Driver");
connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/test",
"root", "000000"); //语句块与预处理语句块的定义 statement=connection.createStatement(); pstmt=connection.prepareStatement("select * from student where sex=?"); //结果的处理 resultset=statement.executeQuery("select * from student"); while(resultset.next()){ System.out.print(resultset.getString(1)+" "); System.out.print(resultset.getString("name")+" "); System.out.print(resultset.getInt(3)+" "); System.out.print(resultset.getString("sex")+"\t"); } //批处理 statement.addBatch("insert into student values(0755010137,'DDL',21,'m')"); statement.addBatch("insert into student values(0755010101,'XWL',22,'m')"); statement.executeBatch(); //预处理语句 pstmt.setString(1, "m"); resultset=pstmt.executeQuery(); while(resultset.next()){ System.out.print(resultset.getString(1)+" "); System.out.print(resultset.getString("name")+" "); System.out.print(resultset.getInt(3)+" "); System.out.print(resultset.getString("sex")+"\t"); } pstmt.setString(1, "w"); resultset=pstmt.executeQuery(); while(resultset.next()){ System.out.print(resultset.getString(1)+" "); System.out.print(resultset.getString("name")+" "); System.out.print(resultset.getInt(3)+" "); System.out.print(resultset.getString("sex")+"\t"); } } catch (SQLException e) { System.out.println("SQLException"); } catch (ClassNotFoundException e) { System.out.println("ClassNotFoundException"); } } }