关于JDBC的连接方式

原创 于 2014-01-18 16:11:42 发布 · 562 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#jdbc #数据库 #通讯录

1、确定使用的驱动程序类

常见数据库的驱动程序类名称如下：

Oracle：驱动程序类名一般为oracle.jdbc.driver.OracleDriver。

SQL Server:驱动程序类名一般为com.microsof.jdbc.sqlserver.SQLServerDriver。

MySql：驱动程序类名一般为com.mysql.jdbc.Driver。

2、确定链接的URL

ORACLE的URL格式为：jdbc:oracle:thin:@[IP]:1521:[SID]。其中ip可使用一下几种方式，如127.0.0.1、localhost和具体的ip地址。例如：jdbc:oracle:thin:@localhost:1521:ORCL。

SQL SERVER的URL格式为：com.microsof.sqlserver://[IP]:1433;DatabaseName=[DBName];user=[user];password=[password]。其中DatabaseName为数据实例名称，user为使用的用户，password为与用户相对应的密码，例如：com.microsof.sqlserver://localhost:1433;DatabaseName=SCHOOL

MYSQL的URL格式为：jdbc:mysql://localhost:3306/[DBName]。其中DBName为数据库实例名称，例如:

jdbc:mysql://localhost:3306/SCHOOL。

其中1521、1433、3306都是安装数据库后的端口号，是唯一的。


下面是个人写的查询数据库获得通讯录信息

工具类DBUtil。此类是获得Connection连接，连接与数据库的通道

package cn.com.db;

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

public class DBUtil {

//测试用的，只测试能否得到联系
public static void main(String[] args) {
DBUtil db=new DBUtil();
System.out.println(db.getConn());
}

public Connection getConn(){
Connection conn=null;

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url="jdbc:oracle:thin:@127.0.0.1:1521:ZYIC";
String user="scott";
String password="tiger";
conn=DriverManager.getConnection(url,user,password);

} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
return conn;
}

public void close(ResultSet rs,Statement st,Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

public void close(Statement st,Connection conn){
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}

DAO类，具体获得哪些信息，每个方法都具体的说明了。

package cn.com.daos;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import cn.com.beans.AddressListBean;
import cn.com.db.DBUtil;


public class AddressListDao {
DBUtil db = null;
PreparedStatement pstm = null;
ResultSet rs = null;
Connection conn = null;

public AddressListDao() {
db = new DBUtil();
}



//根据用户名获取用户信息
public AddressListBean getBeanByUserName( String userName ) {
System.out.println("获得名称为"+userName);
AddressListBean addr = null;
conn = db.getConn();
String sql = "SELECT * FROM ADDRESS_LIST WHERE USERNAME=?";
try {
pstm = conn.prepareStatement(sql);
pstm.setString( 1,userName );
rs = pstm.executeQuery();
if( rs.next() ) {
addr = new AddressListBean();
addr.setUserName( rs.getString( "username" ) );
addr.setPhone( rs.getString( "phone" ) );
addr.setEmail( rs.getString( "email" ) );
addr.setAddress( rs.getString( "address" ) );
addr.setmText( rs.getString( "mtext" ) );
addr.setUserPwd( rs.getString( "userpwd" ) );
addr.setUstat( rs.getInt( "ustat" ) );
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
db.close(rs, pstm, conn);
}
System.out.println(addr.getmText());
return addr;
}


//获得所有用户信息
public List<AddressListBean> getUserInfo() {
List<AddressListBean> list = new ArrayList<AddressListBean>();
AddressListBean addr = null;
String sql = "SELECT * FROM ADDRESS_LIST";
conn = db.getConn();
try {
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
while( rs.next() ) {
addr = new AddressListBean();
addr.setUserName( rs.getString( "username" ) );
addr.setPhone( rs.getString( "phone" ) );
addr.setEmail( rs.getString( "email" ) );
addr.setAddress( rs.getString( "address" ) );
addr.setmText( rs.getString( "mtext" ) );
addr.setUserPwd( rs.getString( "userpwd" ) );
addr.setUstat( rs.getInt( "ustat" ) );
list.add( addr );
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
db.close(rs, pstm, conn);
}
return list;
}



//验证用户以及密码
public boolean validateByNameAndPwd( String userName,String userPwd ) {
boolean bool = false;
conn = db.getConn();
String sql="SELECT * from ADDRESS_LIST WHERE USERNAME=? AND USERPWD=?";
try {
pstm = conn.prepareStatement(sql);
pstm.setString( 1,userName );
pstm.setString( 2,userPwd );
rs = pstm.executeQuery();
if( rs.next() ) {
bool = true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
db.close(rs, pstm, conn);
}
return bool;
}


//删除用户信息
public boolean delUserByName( String userName ) {
boolean bool = false;
conn = db.getConn();
String sql="DELETE FROM ADDRESS_LIST WHERE USERNAME=?";
try {
pstm = conn.prepareStatement(sql);
pstm.setString( 1,userName );
int i = pstm.executeUpdate();
if( i > 0 ) {
bool = true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
db.close(pstm, conn);
}
return bool;
}


//增加用户
public boolean addUser( AddressListBean addr ) {
boolean bool = false;
conn = db.getConn();
String sql = "INSERT INTO ADDRESS_LIST VALUES(USERSEQ.NEXTVAL,?,?,?,?,?,?,?)";
try {
pstm = conn.prepareStatement(sql);
pstm.setString( 1,addr.getUserName() );
pstm.setString( 2,addr.getPhone() );
pstm.setString( 3,addr.getEmail() );
pstm.setString( 4,addr.getAddress() );
pstm.setString( 5,addr.getmText() );
pstm.setString( 6,addr.getUserPwd() );
pstm.setInt( 7,addr.getUstat() );

int i = pstm.executeUpdate();
if( i > 0 ) {
bool = true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
db.close(pstm, conn);
}
return bool;
}


//查询用户名是否存在  
public boolean existByName( String userName ) {
boolean bool = false;
conn = db.getConn();
String sql = "SELECT * FROM ADDRESS_LIST WHERE USERNAME=?";
try {
pstm = conn.prepareStatement(sql);
pstm.setString( 1,userName );
rs = pstm.executeQuery();
if( rs.next() ) {
bool = true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
db.close(rs, pstm, conn);
}
return bool;
}



//查询用户名包含指定字符
public List<AddressListBean> getListByUserInfo(String querytext) {
// TODO Auto-generated method stub
List<AddressListBean> list = new ArrayList<AddressListBean>();
AddressListBean addr = null;
conn = db.getConn();
String sql = "SELECT * FROM ADDRESS_LIST WHERE USERNAME LIKE ?";
try {
pstm = conn.prepareStatement(sql);
pstm.setString( 1,"%"+querytext+"%" );
rs = pstm.executeQuery();
while( rs.next() ) {
addr = new AddressListBean();
addr.setUserName( rs.getString( "username" ) );
addr.setPhone( rs.getString( "phone" ) );
addr.setEmail( rs.getString( "email" ) );
addr.setAddress( rs.getString( "address" ) );
addr.setmText( rs.getString( "mtext" ) );
addr.setUserPwd( rs.getString( "userpwd" ) );
addr.setUstat( rs.getInt( "ustat" ) );
list.add( addr );
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
db.close(rs, pstm, conn);
}
return list;
}


//修改用户信息
public boolean updateUserInfo( AddressListBean addr ) {
boolean bool = false;
conn = db.getConn();
System.out.println("xingming是"+addr.getUserName());
System.out.println("电话号码是"+addr.getPhone());
System.out.println("email是"+addr.getEmail());
System.out.println("address是"+addr.getAddress());
System.out.println("mtext是"+addr.getmText());
String sql = "UPDATE ADDRESS_LIST SET PHONE=?,EMAIL=?,ADDRESS=?,MTEXT=?,USERPWD=? WHERE USERNAME=?";
try {
pstm = conn.prepareStatement(sql);
pstm.setString( 1, addr.getPhone());
pstm.setString( 2, addr.getEmail());
pstm.setString( 3, addr.getAddress());
pstm.setString( 4, addr.getmText());
pstm.setString( 5, addr.getUserPwd());
pstm.setString( 6, addr.getUserName());
int i = pstm.executeUpdate();
if( i > 0 ) {
bool = true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
db.close(pstm, conn);
}
return bool;
}
}




























评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值