1、Day01;
JavaData Base Connention,Java数据库连接;
是一种用于执行SQL语句的javaAPI,可以为多种关系数据库提供统一访问,他是由一组用java语言编写的类和接口组成
JDBC为数据库开发人员提供了一个标准的API,据此可以构建更高级的工具和接口,同时,JDBC也是一个商标名
有sun公司提供访问数据库的接口,由各个数据库厂商提供对这些接口的实现,程序员编写时都是针对接口进行编程的。有了JDBC,向各种关系数据发送SQL语句就是一件很容易的事。换言之,有了JDBCAPI,就不必为访问MySql数据库专门写一个程序,为访问Oracle数据库又专门写一个程序,或为访问其他数据库再编写另一个程序等等,程序员只需用JDBCAPI的标准写一个程序就可向各种数据库发送SQL的调用(前提是有各个数据库厂商提供的驱动包)。同时,将Java语言和JDBC结合起来使程序员不必为不同的平台编写不同的应用程序,只须写一遍程序就可以让它在任何平台上运行,这也是Java语言“编写一次,处处运行”的优势。
JDBC包括一套JDBC的API和一套程序员和数据库厂商都必须去遵守的规范,其主要分两个部分组成,都是包含在java平台里面
java.sql包: 提供访问数据库基本的功能
javax.sql包:提供扩展的功能
驱动:sun.jdbc.odbc.JdbcOdbcDriver
URL:jdbc:odbc:datasource_name
驱动:oracle.jdbc.driver.OracleDriver
URL:jdbc:oracle:thin:@<machine_name><:port>:dbname
注:machine_name:数据库所在的机器的名称;
port:端口号,默认是1521
驱动:org.gjt.mm.mysql.Driver
or:com.mysql.jdbc.Driver
URL:jdbc:mysql://<machine_name><:port>/dbname
注:machine_name:数据库所在的机器的名称;
port:端口号,默认3306
DriverManager.getConnection("jdbc:mysql://192.168.1.2:3306/student_manager","root","admin"
驱动:com.microsoft.jdbc.sqlserver.SQLServerDriver
URL:jdbc:microsoft:sqlserver://<machine_name><:port>;DatabaseName=<dbname>
注:machine_name:数据库所在的机器的名称;
port:端口号,默认是1433
驱动:com.ibm.db2.jdbc.app.DB2Driver
URL:jdbc:db2://<machine_name><:port>/dbname
注:machine_name:数据库所在的机器的名称;
port:端口号,默认是5000
驱动:org.postgresql.Driver
URL: jdbc:postgresql://localhost:5432/test
-
注册驱动;
-
建立驱动;
-
创建执行SQL的对象;
-
执行SQL;
-
如果有结果集返回,则处理结果集;
-
关闭资源;
那么我们就通过mysql和oracle数据库来做一个简单连接数据库的操作吧,看如下代码;
通过maysql:
packagejdbc_p1_mysql_oracle;
importjava.sql.Connection; importjava.sql.DriverManager; importjava.sql.ResultSet; importjava.sql.Statement;
publicclassJdbcTest1_mysql {
publicstaticvoidmain(String[] args) throwsException { Class.forName("com.mysql.jdbc.Driver");//注册驱动; Connectionconn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/test","root","admin");//建立连接; Statementsta = conn.createStatement(); Stringsql = "select now()";//创建执行SQL对象 ResultSetrset = sta.executeQuery(sql);//执行SQL并返回结果集且处理结果集,将其打印出来 if(rset.first()) { System.out.println(rset.getTimestamp(1)); } //关闭连接; rset.close(); sta.close(); conn.close(); } } |
通过oracle:
packagejdbc_p1_mysql_oracle;
importjava.sql.Connection; importjava.sql.DriverManager; importjava.sql.ResultSet; importjava.sql.Statement;
publicclassJdbcTest2_oracle {
publicstaticvoidmain(String[] args) throwsException { Class.forName("oracle.jdbc.driver.OracleDriver");//注册驱动; Connectionconn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl","scott","scott");//建立连接; Statementsta = conn.createStatement();//创建执行SQL对象 Stringsql = "select sysdate fromdual"; ResultSetrset = sta.executeQuery(sql);//执行SQL并返回结果集且处理结果集,将其打印出来 if(rset.next()) { System.out.println(rset.getTimestamp(1)); } rset.close(); sta.close(); conn.close(); } } |
-
Driver接口;
-
DriverManager类
-
Connection接口;
DatabaseMetadata接口数据库元数据
-
Statement接口 用于执行静态SQL语句并返回它所生成的对象据
PreparedStatement接口 表示预编译的 SQL语句的对象。
CallableStatement接口
-
ResultSet
first();
last();
next();
previous();
getxxx(index);
getxxx(colnumName);
那么我们就一一详细介绍以上的常用接口与类:
-
connection:
packagejdbc_p2;
importjava.sql.Connection;
importjava.sql.DriverManager;
publicclassJdbcTest1_connection {
publicstaticvoidmain(String[] args) throwsException {
Class.forName("com.mysql.jdbc.Driver");
Connectionconn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test","root","admin");
System.out.println(DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test","root","admin"));
System.out.println(DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test","root","admin"));
//getConnection方法不是单例方法.每调用一次就会获得一个新的Connection对象.
//connection对象默认是自动提交事务
//为了方便程序员编写事务代码,需要关闭自动提交
conn.setAutoCommit(false);
conn.close();
}
}
-
DatabaseMetaData;
packagejdbc_p2;
importjava.sql.Connection;
importjava.sql.DatabaseMetaData;
importjava.sql.DriverManager;
publicclassJdbcTest2_databaseMetaData {
publicstaticvoidmain(String[] args) throwsException {
Class.forName("com.mysql.jdbc.Driver");
Connectionconn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test","root","admin");
System.out.println(DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test","root","admin"));
System.out.println(DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test","root","admin"));
//getConnection方法不是单例方法.每调用一次就会获得一个新的Connection对象.
DatabaseMetaDatadmdata = conn.getMetaData();
System.out.println(dmdata.getDatabaseProductName());
System.out.println(dmdata.getDatabaseProductVersion());
System.out.println(dmdata.getDriverName());
System.out.println(dmdata.getDriverVersion());
System.out.println(dmdata.getURL());
System.out.println(dmdata.getUserName());
/*
* ResultSet rset = dmdata.getTables(null, "root",null, null);
* while(rset.next()){ System.out.print(rset.getString(1)+"");
* System.out.print(rset.getString(2)+" ");
* System.out.print(rset.getString(3)+" ");
* System.out.print(rset.getString(4)+" ");
* System.out.println(rset.getString(5)+" ");
* System.out.print(rset.getString(6)+" ");
* System.out.print(rset.getString(7)+" ");
* System.out.print(rset.getString(8)+" ");
* System.out.print(rset.getString(9)+" ");
* System.out.println(rset.getString(10)); }
*/
conn.close();
}
}
-
Statement;
packagejdbc_p2;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
publicclassJdbcTest3_statement {
publicstaticvoidmain(String[] args)throwsException {
Class.forName("com.mysql.jdbc.Driver");
Connectionconn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test","root","admin");
conn.setAutoCommit(false);
//创建statement对象,用于执行静态SQL
Statementsta = conn.createStatement();
Stringsql = "select now()";// 静态SQL
ResultSetrset = sta.executeQuery(sql);
if(rset.next()) {
System.out.println(rset.getTimestamp(1));
}
statementTest(sta,"def");
conn.commit();
rset.close();
conn.close();
}
publicstaticvoidstatementTest(Statement sta, String var)
throwsSQLException {
Stringsql2 = "insert into test2values(null,'" + var +"')";//依然是静态SQL
sta.executeUpdate(sql2);
}
}
-
statement execute dml;
packagejdbc_p2;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.ResultSet;
importjava.sql.Statement;
publicclassJdbcTest4_statement_execute_dml {
publicstaticvoidmain(String[] args) throwsException {
Class.forName("com.mysql.jdbc.Driver");
Connectionconn =DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","admin");
conn.setAutoCommit(false);
Statementsta = conn.createStatement();
//定义sql
Stringsql = "select now()";//select语句
sql= "insert into test2 (var)values ('def')";
//执行方法1:返回boolean值,如果返回的是true表示执行的是select语句,需要从sta对象获得一个结果集.如果返回的是false,表示执行的是dml语句,需要从sta对象获得整数
booleanisQuery = sta.execute(sql);
if(isQuery){
System.out.println("执行的是select语句,需要从Statement对象中获得执行完sql返回的结果集");
ResultSetrset = sta.getResultSet();
}else{
System.out.println("执行的是DML语句,需要从Statement对象中获得执行完sql返回的受影响的数据行数");
intupdateRowCount = sta.getUpdateCount();
System.out.println("受影响的记录行数:"+ updateRowCount);
}
//执行方法2: 执行SQL,返回一个结果集. ==>只适合执行select语句.
ResultSetrset= sta.executeQuery("selectnow()");
//rset= sta.executeQuery("insert into test2 (var)values ('def')");//Can not issue data manipulation statements withexecuteQuery()
//执行方法3: 执行DML语句,返回受影响的数据行数.===> 只适合执行DML语句
introwCount= sta.executeUpdate("insertinto test2 (var) values ('def')");
//rowCount= sta.executeUpdate("select now()"); //Can not issueSELECT via executeUpdate()
//执行方法4:批处理
sta.addBatch("insertinto test2 (var) values ('abc1')");
sta.addBatch("insertinto test2 (var) values ('abc2')");
sta.addBatch("deletefrom test2 where var = 'abc2'");
sta.addBatch("insertinto test2 (var) values ('abc3')");
sta.addBatch("insertinto test2 (var) values ('abc4')");
sta.executeBatch();
conn.commit();
conn.close();
}
}
-
statement execute ddl;
packagejdbc_p2;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.Statement;
publicclassJdbcTest5_statement_execute_ddl {
publicstaticvoidmain(String[] args) throwsException {
Class.forName("com.mysql.jdbc.Driver");
Connectionconn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test","root","admin");
conn.setAutoCommit(false);
Statementsta = conn.createStatement();
StringcreateUserTableSQL = "createtable user(id int primary key auto_increment,namevarchar(20),password varchar(6))";
StringcreateUserInfoTableSQL = "createtable user_info(id int primary key references user(id),telchar(11),adds varchar(50),email varchar(30))";
StringalterUserInfoTableSql = "altertable user_info add sex char(2)";
sta.addBatch(createUserInfoTableSQL);
sta.addBatch(createUserTableSQL);
sta.addBatch(alterUserInfoTableSql);
sta.executeBatch();
conn.commit();
conn.close();
}
}
-
statement preparedStatement;
packagejdbc_p2;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
publicclassJdbcTest6_statement_preparedStatement {
publicstaticvoidmain(String[] args) {
Connectionconn = null;
try{
Class.forName("com.mysql.jdbc.Driver");
conn= DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test","root","admin");
//关闭事务自动提交
conn.setAutoCommit(false);
//调用插入用户数据的业务方法.
//insertUser(conn,"james2","123123","1231231231","guangzhou","a@b.c","男");
insertUser2(conn,"lily","123123","1231231231","guangzhou",
"a@b.c","女");
//执行没有异常,提交事务.
conn.commit();
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(SQLException e) {
e.printStackTrace();
try{
conn.rollback();
}catch(SQLException e1) {
e1.printStackTrace();
}
}finally{
try{
if(conn != null)
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
publicstaticvoidinsertUser(Connection conn, String name,
Stringpassword, String tel, String adds, String email, String sex)
throwsSQLException {
//Statment
Statementsta = conn.createStatement();
//intid,String name,String password 保存在user表
Stringsql1 = "insert into uservalues(null,'" + name +"','"
+password + "')";
sta.execute(sql1);
//检索出之前插入的用户的id,作为子表中外键的值.
Stringmsql = "select id from userwhere name = '" + name
+"' and password = '"+ password + "'";
ResultSetrset = sta.executeQuery(msql);
intid = -1;
if(rset.first()) {
id= rset.getInt(1);
}
//intuid,Stringtel,Stringadds,String email,String sex 保存在user_info表
Stringsql2 = "insert intouser_info values(" + id +",'"+ tel + "','"
+adds + "','"+ email + "','"+ sex + "')";
sta.executeUpdate(sql2);
}
publicstaticvoidinsertUser2(Connection conn, String name,
Stringpassword, String tel, String adds, String email, String sex)
throwsSQLException {
//intid,String name,String password 保存在user表
Stringsql1 = "insert into uservalues(null,?,?)";
//检索出之前插入的用户的id,作为子表中外键的值.
Stringmsql = "select id from userwhere name = ? and password = ?";
//intuid,Stringtel,Stringadds,String email,String sex 保存在user_info表
Stringsql2 = "insert intouser_info values(?,?,?,?,?)";
//PreparedStatement 表示预编译的SQL语句的对象。
PreparedStatementpsta1 = conn.prepareStatement(sql1);
//设置预编译SQL的参数.
psta1.setString(1,name);
psta1.setString(2,password);
//执行
psta1.executeUpdate();
PreparedStatementpsta2 = conn.prepareStatement(msql);
psta2.setString(1,name);
psta2.setString(2,password);
ResultSetrset = psta2.executeQuery();
intid = -1;
if(rset.first()) {
id= rset.getInt(1);
}
PreparedStatementpsta3 = conn.prepareStatement(sql2);
psta3.setInt(1,id);
psta3.setString(2,tel);
psta3.setString(3,adds);
psta3.setString(4,email);
psta3.setString(5,sex);
psta3.executeUpdate();
}
}
-
resultSet;
packagejdbc_p2;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
publicclassJdbcTest8_resultSet {
publicstaticvoidmain(String[] args) {
Connectionconn = null;
try{
Class.forName("com.mysql.jdbc.Driver");
conn= DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test","root","admin");
Statementsta = conn.createStatement();
Stringsql = "select * from userwhere 1=2";
//执行select语句返回结果集.
ResultSetrset = sta.executeQuery(sql);
//哪怕select检错不出任何数据行,也会返回一个结果集.
System.out.println(rset== null);
//有可能返回多条数据行.
sql= "select * from user";
rset= sta.executeQuery(sql);
//循环操作结果集
while(rset.next()) { //让结果集的光标下移一行,有数据返回true.
//从结果集取数据
//getxxx(index),index是只查询出来的数据行中字段的索引位置,从1开始.
intid = rset.getInt(1);
Stringpassword = rset.getString(3);
Stringname = rset.getString(2);
System.out.println(rset.getRow()+ " "+ id + " "+ name + " "
+password);
}
System.out.println();
sql= "select * from user";
rset= sta.executeQuery(sql);
//循环操作结果集
while(rset.next()) {
//从结果集取数据
//getxxx(colnumName),
Stringname = rset.getString("name");
intid = rset.getInt("id");
Stringpassword = rset.getString("password");
System.out.println(id+ " "+ name + " "+ password);
}
//==============================
//返回一行的
sql= "select count(*) fromuser";
rset= sta.executeQuery(sql);
//如果我们明确结果集返回一行,我们可以用first方法,让光标指向第一行来判断是否有数据
if(rset.first()) {
System.out.println("总共有用户:"+ rset.getInt(1));
}
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(SQLException e) {
e.printStackTrace();
}finally{
try{
if(conn != null)
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}
-
通过操作对象的方式来操作数据库的例子吧;
-
为不同的表来创建一个javabean对象
userbean<> user表: packagejdbc_p2.model;
publicclassUser { privateInteger id; privateString name; privateString password;
//聚合了用户信息对象. privateUserInfo userInfo;
publicUserInfo getUserInfo() { returnuserInfo; }
publicvoidsetUserInfo(UserInfo userInfo) { this.userInfo= userInfo; //双向关联 userInfo.setUser(this); }
publicUser() { super(); }
publicUser(String name, String password) { super(); this.name= name; this.password= password; }
publicUser(Integer id, String name, String password) { super(); this.id= id; this.name= name; this.password= password; }
publicintgetId() { returnid; }
publicvoidsetId(Integer id) { this.id= id; }
publicString getName() { returnname; }
publicvoidsetName(String name) { this.name= name; }
publicString getPassword() { returnpassword; }
publicvoidsetPassword(String password) { this.password= password; } } |
表中有主外键,在javabean中可以通过聚合 双向关联来实现
userInfobean <> userInfo 表 packagejdbc_p2.model;
publicclassUserInfo { privateInteger id; privateString tel; privateString adds; privateString email; privateString sex;
privateUser user;
publicUser getUser() { returnuser; }
publicvoidsetUser(User user) { this.user= user; }
publicUserInfo() { super(); //TODOAuto-generated constructor stub }
publicUserInfo(String tel, String adds, String email, String sex) { super(); this.tel= tel; this.adds= adds; this.email= email; this.sex= sex; }
publicUserInfo(Integer id, String tel, String adds, String email, Stringsex) { super(); this.id= id; this.tel= tel; this.adds= adds; this.email= email; this.sex= sex; }
publicInteger getId() { returnid; }
publicvoidsetId(Integer id) { this.id= id; }
publicString getTel() { returntel; }
publicvoidsetTel(String tel) { this.tel= tel; }
publicString getAdds() { returnadds; }
publicvoidsetAdds(String adds) { this.adds= adds; }
publicString getEmail() { returnemail; }
publicvoidsetEmail(String email) { this.email= email; }
publicString getSex() { returnsex; }
publicvoidsetSex(String sex) { this.sex= sex; } } |
对象 关系 映射
packagejdbc_p2;
importjava.sql.Connection; importjava.sql.DriverManager; importjava.sql.PreparedStatement; importjava.sql.ResultSet; importjava.sql.SQLException; importjava.sql.Statement;
importjdbc_p2.model.User; importjdbc_p2.model.UserInfo;
publicclassJdbcTest7_orm {
publicstaticvoidmain(String[] args) { Connectionconn = null; try{ Class.forName("com.mysql.jdbc.Driver"); conn= DriverManager.getConnection( "jdbc:mysql://localhost:3306/test","root","admin"); //关闭事务自动提交 conn.setAutoCommit(false);
//调用插入用户数据的业务方法. //insertUser(conn,"james2","123123","1231231231","guangzhou","a@b.c","男"); //insertUser2(conn,"lily","123123","1231231231","guangzhou","a@b.c","女");
Useruser = newUser("lily2","123123"); UserInfouserInfo = newUserInfo("1231231231","guangzhou", "a@b.c","女"); user.setUserInfo(userInfo); insertUser2(conn,user);
//执行没有异常,提交事务. conn.commit(); }catch(ClassNotFoundException e) { e.printStackTrace(); }catch(SQLException e) { e.printStackTrace(); try{ conn.rollback(); }catch(SQLException e1) { e1.printStackTrace(); } }finally{ try{ if(conn != null) conn.close(); }catch(SQLException e) { e.printStackTrace(); } } }
publicstaticvoidinsertUser(Connection conn, User user) throwsSQLException { //Statment Statementsta = conn.createStatement();
//intid,String name,String password 保存在user表 Stringsql1 = "insert into uservalues(null,'" + user.getName()+ "','" +user.getPassword() + "')"; sta.execute(sql1);
//检索出之前插入的用户的id,作为子表中外键的值. Stringmsql = "select id from userwhere name = '" + user.getName() +"' and password = '"+ user.getPassword() + "'"; ResultSetrset = sta.executeQuery(msql); intid = -1; if(rset.first()) { id= rset.getInt(1); }
//intuid,Stringtel,Stringadds,String email,String sex 保存在user_info表 Stringsql2 = "insert into user_infovalues(" + id + ",'" +user.getUserInfo().getTel() + "','" +user.getUserInfo().getAdds() + "','" +user.getUserInfo().getEmail() + "','" +user.getUserInfo().getSex() + "')"; sta.executeUpdate(sql2); }
publicstaticvoidinsertUser2(Connection conn, User user) throwsSQLException { //intid,String name,String password 保存在user表 Stringsql1 = "insert into uservalues(null,?,?)"; //检索出之前插入的用户的id,作为子表中外键的值. Stringmsql = "select id from userwhere name = ? and password = ?"; //intuid,Stringtel,Stringadds,String email,String sex 保存在user_info表 Stringsql2 = "insert into user_infovalues(?,?,?,?,?)";
//PreparedStatement 表示预编译的SQL语句的对象。 PreparedStatementpsta1 = conn.prepareStatement(sql1); //设置预编译SQL的参数. psta1.setString(1,user.getName()); psta1.setString(2,user.getPassword()); //执行 psta1.executeUpdate();
PreparedStatementpsta2 = conn.prepareStatement(msql); psta2.setString(1,user.getName()); psta2.setString(2,user.getPassword()); ResultSetrset = psta2.executeQuery(); intid = -1; if(rset.first()) { id= rset.getInt(1); }
PreparedStatementpsta3 = conn.prepareStatement(sql2); psta3.setInt(1,id); psta3.setString(2,user.getUserInfo().getTel()); psta3.setString(3,user.getUserInfo().getAdds()); psta3.setString(4,user.getUserInfo().getEmail()); psta3.setString(5,user.getUserInfo().getSex()); psta3.executeUpdate(); } } |
数据库元数据
结果集元数据
-
连接池;
-