-----------android培训、java培训、期待与您交流!-----------
程序详解—DriverManager
l Jdbc程序中的DriverManager用于加载驱动,并创建与数据库的链接,这个API的常用方法:
• DriverManager.registerDriver(new Driver())
• DriverManager.getConnection(url, user, password),
• 注意:在实际开发中并不推荐采用registerDriver方法注册驱动。原因有二:
一、查看Driver的源代码可以看到,如果采用此种方式,会导致驱动程序注册两次,也就是在内存中会有两个Driver对象。
二、程序依赖mysql的api,脱离mysql的jar包,程序将无法编译,将来程序切换底层数据库将会非常麻烦。
• 推荐方式:Class.forName(“com.mysql.jdbc.Driver”);
• 采用此种方式不会导致驱动对象在内存中重复出现,并且采用此种方式,程序仅仅只需要一个字符串,不需要依赖具体的驱动,使程序的灵活性更高。
• 同样,在开发中也不建议采用具体的驱动类型指向getConnection方法返回的connection对象。
l URL用于标识数据库的位置,程序员通过URL地址告诉JDBC程序连接哪个数据库,URL的写法为:
jdbc:mysql:[]//localhost:3306/test ?参数名:参数值
l 常用数据库URL地址的写法:
• Oracle写法:jdbc:oracle:thin:@localhost:1521:sid
• SqlServer—jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=sid
• MySql—jdbc:mysql://localhost:3306/sid
l Mysql的url地址的简写形式:jdbc:mysql:///sid
l 常用属性:useUnicode=true&characterEncoding=UTF-8
Jdbc关闭资源模板代码
package cn.itcast.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import cn.itcast.domain.User;
public class Demo2 {
/**
create database day14;
use day14;
create table users(
idint primary key,
namevarchar(40),
passwordvarchar(40),
emailvarchar(60),
birthdaydate
);
insertinto users(id,name,password,email,birthday)values(1,'zs','123456','zs@sina.com','1980-12-04');
insertinto users(id,name,password,email,birthday)values(2,'lisi','123456','lisi@sina.com','1981-12-04');
insertinto users(id,name,password,email,birthday)values(3,'wangwu','123456','wangwu@sina.com','1979-12-04');
*/
publicstatic void main(String[] args) throws SQLException, ClassNotFoundException {
Stringurl = "jdbc:mysql:///day14";
Stringusername = "root";
Stringpassword = "root";
Connectionconn = null;
Statementst = null;
ResultSetrs = null;
try{
Class.forName("com.mysql.jdbc.Driver");
conn= DriverManager.getConnection(url, username, password);
st= conn.createStatement(); //throw
rs= st.executeQuery("select * from users");
while(rs.next()){
Useruser = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
user.setBirthday(rs.getDate("birthday"));
}
}finally{
if(rs!=null){
try{
rs.close(); //throw new
}catch(Exception e) {
e.printStackTrace();
}
rs= null;
}
if(st!=null){
try{
st.close();
}catch (Exception e) {
e.printStackTrace();
}
st= null;
}
if(conn!=null){
try{
conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
}
}
Jdbc工具类
package cn.itcast.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtils {
privatestatic Properties config = new Properties();
static{
try{
config.load(JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"));
Class.forName(config.getProperty("driver"));
}catch (Exception e) {
thrownew ExceptionInInitializerError(e);
}
}
publicstatic Connection getConnection() throws SQLException{
returnDriverManager.getConnection(config.getProperty("url"),config.getProperty("username"),config.getProperty("password"));
}
publicstatic void release(Connection conn,Statement st,ResultSet rs){
if(rs!=null){
try{
rs.close(); //throw new
}catch(Exception e) {
e.printStackTrace();
}
rs= null;
}
if(st!=null){
try{
st.close();
}catch(Exception e) {
e.printStackTrace();
}
st= null;
}
if(conn!=null){
try{
conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
}
Db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day14
username=root
password=root
#driver=oracle.jdbc.driver.OracleDriver
#url=jdbc:oracle:thin:@localhost:1521:orcl
#username=system
#password=itcast
Jdbc增删改查
package cn.itcast.demo;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import cn.itcast.domain.User;
import cn.itcast.utils.JdbcUtils;
public class Demo3 {
@Test
publicvoid insert() throws SQLException{
Connectionconn = null;
Statementst = null;
ResultSetrs = null;
try{
conn= JdbcUtils.getConnection(); //oracle
st= conn.createStatement();
Stringsql = "insert into users(id,name,password,email)values(4,'eee','123','ee@sina.com')";
intnum = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功!!!");
}
}finally{
JdbcUtils.release(conn,st, rs);
}
}
@Test
publicvoid update() throws SQLException{
Connectionconn = null;
Statementst = null;
ResultSetrs = null;
try{
conn= JdbcUtils.getConnection();
st= conn.createStatement();
String sql = "updateusers set name='fff' where id='4'";
intnum = st.executeUpdate(sql);
if(num>0){
System.out.println("更新成功!!");
}
}finally{
JdbcUtils.release(conn,st, rs);
}
}
@Test
publicvoid delete() throws SQLException{
Connection conn = null;
Statementst = null;
ResultSetrs = null;
try{
conn= JdbcUtils.getConnection();
st= conn.createStatement();
Stringsql = "delete from users where id=4";
intnum = st.executeUpdate(sql);
if(num>0){
System.out.println("删除成功!!");
}
}finally{
JdbcUtils.release(conn,st, rs);
}
}
publicvoid find() throws SQLException{
Connectionconn = null;
Statementst = null;
ResultSetrs = null;
try{
conn= JdbcUtils.getConnection();
st= conn.createStatement();
Stringsql = "select id,name,password,email,birthday from users where id=1";
rs = st.executeQuery(sql);
Useruser = null;
if(rs.next()){
user= new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
user.setBirthday(rs.getDate("birthday"));
}
System.out.println(user);
}finally{
JdbcUtils.release(conn,st, rs);
}
}
@Test
publicvoid getAll() throws SQLException{
Connectionconn = null;
Statementst = null;
ResultSetrs = null;
try{
conn= JdbcUtils.getConnection();
st= conn.createStatement();
Stringsql = "select id,name,password,email,birthday from users";
rs = st.executeQuery(sql);
Listlist = new ArrayList();
while(rs.next()){
Useruser = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
user.setBirthday(rs.getDate("birthday"));
list.add(user);
}
System.out.println(list);
}finally{
JdbcUtils.release(conn,st, rs);
}
}
}