JDBC笔记整理.doc

Day01
一.知识点
1. 什么是JDBC
1）JDBC Java语言用于访问数据库的标准
2）由Sun公司指定
3）各个数据库厂商提供实现

2. JDBC核心API
1）Connnection
2）Statement
3）ResultSet
package com.tarena.day01;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestJABCDemo {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.OracleDriver");
Connection con=DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.0.40:1521:tarena",
"csoracle","tarena");
Statement stmt=con.createStatement();
stmt.execute("select empno,ename from employee_zxw");
ResultSet rs=stmt.getResultSet();
while(rs.next()){
System.out.println(rs.getInt(1)+","+rs.getString(2));
}
}

}

3. JDBC连接数据库步骤
//装载并注册Driver
Class.forName("oracle.jdbc.OracleDriver")

//获取连接
// url oracle: jdbc:oracle:thin:@ip:port:sid
Connection con = DriverManager.getConnection(url,user,pwd)

// 建立Statement并执行SQL
Statement stmt = con.createStatement();
boolean b = stmt.execute("...")
如果是查询(b=true)
ResultSet rs = stmt.getResultSet();
如果不是查询(b=false)
int updateCount = stmt.getUpdateCount();

ResultSet rs = stmt.executeQuery("")
int updateCount = stmt.executeUpdate("")

// 通过ResultSet获得数据或元数据
// 获得数据
while(rs.next()) {
rs.getXXX(int n)
}
//获得元数据
ResultSetMetaData rsmd = rs.getMetaData();
rsmd.getColumnCount();
rsmd.getColumnName();
rsmd.getColumnTypeName();

4.如何做ConnectionUtils
package com.tarena.day01;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class ConnectUtils {
private static String driver;
private static String url;
private static String userid;
private static String password;
static {
try {
// 获得当前类属性路径下的db.properties
Properties props = new Properties();
InputStream is = ConnectUtils.class.getClassLoader()
.getResourceAsStream("db.properties");
props.load(is);
driver = props.getProperty(driver);
url = props.getProperty(url);
userid = props.getProperty(userid);
password = props.getProperty(password);
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}

}

public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, userid, password);

}
}

打印出SQL表中数据类型
package com.tarena.day01;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

import com.tarena.day1.ConnectionUtils;

public class TestJABCDemo {
public static void main(String[] args) throws Exception {
/**传统连接JDBC方式*/
// Class.forName("oracle.jdbc.OracleDriver");
// Connection con=DriverManager.getConnection(
// "jdbc:oracle:thin:@192.168.0.40:1521:tarena",
// "csoracle","tarena");
/**包装后连接JDBC方式*/
Connection con=ConnectionUtils.getConnection();
Statement stmt=con.createStatement();
stmt.execute("select empno,ename from employee_zxw");
ResultSet rs=stmt.getResultSet();
while(rs.next()){
System.out.println(rs.getInt(1)+","+rs.getString(2));
}
//rs.close();
//stmt.close();
/**stmt关闭，stmt2开启*/
Statement stmt1=con.createStatement();
stmt1.execute("insert into employee_zxw(empno,ename) values(1004,'jzf')");
//int count=stmt1.executeUpdate("update");
//System.out.println(count);
int count=stmt1.getUpdateCount();
System.out.println(count);
stmt1.close();
ResultSet rs1=stmt.getResultSet();
ResultSetMetaData rsmd=rs1.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
System.out.print(rsmd.getColumnName(i) + " ");

}
System.out.println();
System.out.println("------------------------------");

while (rs.next()) {
for (int i = 1; i <= columnCount; i++) {
System.out.print(rs1.getString(i) + " ");
}
System.out.println();
}
con.close();
}

}
5.注意程序中的SQL Injection
package com.tarena.day01;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import com.tarena.day1.ConnectionUtils;

public class InsertUser {
public static void main(String[] args) throws SQLException {
Connection con=ConnectionUtils.getConnection();
Statement stmt=con.createStatement();
for(int i=0;i<100;i++){
stmt.execute("insert into emp_bakzxw(time1,time2) values(1000,1000)");//此处眼严格尊重SQL语句，最好建议PrepareStatment
}
ResultSet rs = stmt.executeQuery("select * from emp_bakzxw");
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
/**打印表中数据成员*/
for (int i = 1; i <= columnCount; i++) {
System.out.print(rsmd.getColumnName(i) + " ");
}
System.out.println();
System.out.println("------------------------------");
/**打印表内内容*/
while (rs.next()) {
for (int i = 1; i <= columnCount; i++) {
System.out.print(rs.getString(i) + " ");
}
System.out.println();
}
}

}