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

public class DBTools {
// 定義一個(gè)方法，用來(lái)得到一個(gè)"新的"連接對(duì)象
public static Connection getConnection()
{
Connection conn = null;
String driverName = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:ora9i";
String userName = "scott";
String passWord = "tiger";
try {
Class.forName(driverName);
conn = DriverManager.getConnection(url,userName,passWord );
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}

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

public static void closeState(Statement state)
{
try {
if(state != null)
{
state.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

public static void closeRs(ResultSet rs)
{
try {
if(rs != null)
{
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import com.tianyitime.notebook.support.userPO.UserPO;
import com.tianyitime.notebook.support.util.DBTools;

public class UserDAO {

// 新增user
public void saveUserInfo(UserPO upo)
{
Connection conn = null;
Statement state = null;
try {
conn = DBTools.getConnection();
state = conn.createStatement();
String sql = "insert into notebook_user values ("+getMaxId()+",'"+upo.getYhm()+"','"+upo.getEmail()+"','"+upo.getContent()+"')";
//System.out.println(sql);
state.executeUpdate(sql);

} catch (Exception ex) {
// TODO Auto-generated catch block
ex.printStackTrace();
}
finally
{
DBTools.closeState(state);
DBTools.closeConn(conn);
}
}

//得到一個(gè)數(shù)據(jù)庫(kù)中當(dāng)前Id的最大值
private int getMaxId()
{
Connection conn = null;
Statement state = null;
ResultSet rs = null;
int maxId = 0;
try {
conn = DBTools.getConnection();
state = conn.createStatement();
String sql = "select max(id) maxId from notebook_user";
rs = state.executeQuery(sql);
//從resultset對(duì)象中將數(shù)據(jù)取出
if(rs.next())
{
maxId = rs.getInt("maxId");
}
} catch (Exception ex) {
// TODO Auto-generated catch block
ex.printStackTrace();
}

return ++maxId;
}

// 得到所有的記錄
public ArrayList getUserInfo()
{
Connection conn = null;
Statement state = null;
ResultSet rs = null;
UserPO upo = null;
ArrayList al = new ArrayList();
try {
conn = DBTools.getConnection();
state = conn.createStatement();
String sql = "select * from notebook_user";
rs = state.executeQuery(sql);
//從resultset對(duì)象中將數(shù)據(jù)取出

while(rs.next())
{ 
upo = new UserPO();
int id = rs.getInt("id");
String yhm = rs.getString("yhm");
String email = rs.getString("email");
String content = rs.getString("content");

upo.setId(id);
upo.setYhm(yhm);
upo.setEmail(email);
upo.setContent(content);

//將改對(duì)象放入已經(jīng)創(chuàng)建好的集合類對(duì)象ArrauyList
al.add(upo);
}
} catch (Exception ex) {
// TODO Auto-generated catch block
ex.printStackTrace();
}
finally
{
DBTools.closeRs(rs);
DBTools.closeState(state);
DBTools.closeConn(conn);
}
return al;
}

// 刪除一條user記錄
public void deleteUserInfo(int id)
{
Connection conn = null;
Statement state = null;
try {
conn = DBTools.getConnection();
state = conn.createStatement();
String sql = "delete from notebook_user where id="+id;
//System.out.println(sql);
state.executeUpdate(sql);

} catch (Exception ex) {
// TODO Auto-generated catch block
ex.printStackTrace();
}
finally
{
DBTools.closeState(state);
DBTools.closeConn(conn);
}
}

// 根據(jù)給定的信息得到記錄
public ArrayList getUserInfoByInfo(String name,String email,String content)
{
Connection conn = null;
Statement state = null;
ResultSet rs = null;
UserPO upo = null;
ArrayList al = new ArrayList();
try {
conn = DBTools.getConnection();
state = conn.createStatement();
String sql = "select * from notebook_user where 1=1 ";
if(!"".equals(name) && name != null)
{
sql += " and yhm like '%"+name+"%'";
}
if(!"".equals(email) && email != null)
{
sql += " and email = '"+email+"'";
}
if(!"".equals(content) && content != null)
{
sql += " and content like '%"+content+"%'";
}
sql+=" order by id desc";
rs = state.executeQuery(sql);
//從resultset對(duì)象中將數(shù)據(jù)取出

while(rs.next())
{ 
upo = new UserPO();
int id = rs.getInt("id");
String yhm = rs.getString("yhm");
String femail = rs.getString("email");
String fcontent = rs.getString("content");

upo.setId(id);
upo.setYhm(yhm);
upo.setEmail(femail);
upo.setContent(fcontent);

//將改對(duì)象放入已經(jīng)創(chuàng)建好的集合類對(duì)象ArrauyList
al.add(upo);
}
} catch (Exception ex) {
// TODO Auto-generated catch block
ex.printStackTrace();
}
finally
{
DBTools.closeRs(rs);
DBTools.closeState(state);
DBTools.closeConn(conn);
}
return al;
}

}

標(biāo)簽： 數(shù)據(jù)庫(kù)

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請(qǐng)聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請(qǐng)與原作者聯(lián)系。