与mysql数据库建立连接
package cn.java.mydoc.dao.impl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import javax.resource.cci.ResultSet;
public class BaseDao {
private static String driver="com.mysql.jdbc.Driver";
private static String url="jdbc:mysql://localhost:3306/test1";
private static String user="root";
private static String password="123456";
public static Connection getConnection(){
Connection conn =null;
try{
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
if(!conn.isClosed()){
System.out.println("succeed connecting to database");
}
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
public static void closeAll(Connection conn,Statement stmt,ResultSet rs){
if(rs!=null){
try{
rs.close();
}catch(Exception e){
e.printStackTrace();
}
}
if(stmt!=null){
try{
stmt.close();
}catch(Exception e){
e.printStackTrace();
}
}
if(conn!=null){
try{
conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
}
实现按id查找、更新、和全部遍历
package cn.java.mydoc.dao.impl;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.Connection;
import cn.java.mydoc.bean.Profile;
public class ProfileDao extends BaseDao {
public List<Profile> getAll(){
Connection connection = getConnection();
PreparedStatement pstmt = null;
ResultSet rs =null;
String sql="select * from profile";
List<Profile> profileList = new ArrayList<Profile>();
try{
pstmt = connection.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()){
Profile profile = new Profile();
profile.setid(rs.getInt("id"));
profile.setname(rs.getString("name"));
profile.setbirthday(rs.getString("birthday"));
profile.setgender(rs.getString("gender"));
profile.setcareer(rs.getString("career"));
profile.setaddress(rs.getString("address"));
profile.setmobile(rs.getString("mobile"));
profileList.add(profile);
}
}catch(SQLException e){
e.printStackTrace();
}finally{
closeAll(connection,pstmt,null);
}
return profileList;
}
public void updateProfile(Profile profile){
Connection connection = null;
PreparedStatement pstmt = null;
String sql = "update profile set name=?,birthday=?,gender=?,career=?,address=?,mobile=? where id="+profile.getid();
try{
connection = BaseDao.getConnection();
pstmt = connection.prepareStatement(sql);
pstmt.setString(1, profile.getname());
pstmt.setString(2, profile.getbirthday());
pstmt.setString(3, profile.getgender());
pstmt.setString(4, profile.getcareer());
pstmt.setString(5, profile.getaddress());
pstmt.setString(6, profile.getmobile());
pstmt.execute();
}catch(SQLException e){
e.printStackTrace();
}finally{
closeAll(connection,pstmt,null);
}
}
public Profile getProfile(int id){
Connection connection = getConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select * from profile where id="+id;
Profile profile = new Profile();
try{
pstmt = connection.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()){
profile.setid(rs.getInt("id"));
profile.setname(rs.getString("name"));
profile.setbirthday(rs.getString("birthday"));
profile.setgender(rs.getString("gender"));
profile.setcareer(rs.getString("career"));
profile.setaddress(rs.getString("address"));
profile.setmobile(rs.getString("mobile"));
}
}catch(SQLException e){
e.printStackTrace();
}finally{
closeAll(connection,pstmt,null);
}
return profile;
}
}
下图为数据库表数据
测试类代码
package cn.java.mydoc.biz.impl;
import java.util.List;
import cn.java.mydoc.bean.Profile;
import cn.java.mydoc.dao.impl.ProfileDao;
public abstract class test {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
ProfileDao profiledao = new ProfileDao();
List<Profile> list = profiledao.getAll();
if(list.size()>0){
System.out.println("getAll()ok" );
}else{
System.out.println("getAll()roung");
}
Profile profile = profiledao.getProfile(2);
if(profile!=null){
System.out.println("getProfile()ok");
}else{
System.out.println("getProfile()roung");
}
Profile profile1 = profiledao.getProfile(3);
profile1.setbirthday("isTomo");
profiledao.updateProfile(profile1);
}
}
测试结果: