Java如何使用jdbc连接数据库

BaseDAO

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


public abstract class BaseDAO {
//获取连接对象
protected Connection getConn(){
try {
Class.forName("com.mysql.jdbc.Driver");
return DriverManager.getConnection("jdbc:mysql://localhost:3306/persondb", "root", "ok");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null ;
}

//释放资源
protected void close(ResultSet rs , Statement psmt , Connection conn){
try {
if (rs != null) {
rs.close();
}
if(psmt!=null){
psmt.close();
}
if(conn!=null && !conn.isClosed()){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}

}

PersonDAO

import java.util.List;


import com.gem.java.jdbc.pojo.Person;


public interface PersonDAO {
//添加新person
boolean addPerson(Person person);
//修改特定Person信息
boolean updatePerson(Person person);
//删除特定Person信息
boolean delPerson(int id);
//查询所有Person信息
List<Person> getPersonList();
//查询特定Person信息
Person getPerson(int id);

}

 

PersonDAOImpl

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 com.gem.java.jdbc.base.BaseDAO;
import com.gem.java.jdbc.dao.PersonDAO;
import com.gem.java.jdbc.pojo.Person;


public class PersonDAOImpl extends BaseDAO implements PersonDAO {


private Connection conn;
private PreparedStatement psmt;
private ResultSet rs;


@Override
public boolean addPerson(Person person) {
conn = getConn() ;
String sql = "insert into t_user values(null , ? , ? , ? ) " ;
try {
psmt = conn.prepareStatement(sql);
psmt.setString(1, person.getPname());
psmt.setInt(2, person.getAge());
psmt.setString(3, person.getAddress());

return psmt.executeUpdate() > 0 ; 
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(rs, psmt, conn);
}
return false;
}


@Override
public boolean updatePerson(Person person) {
conn = getConn() ;
String sql = "update t_user set pname = ? , age = ? , address = ? where id = ? " ;
try {
psmt = conn.prepareStatement(sql);
psmt.setString(1, person.getPname());
psmt.setInt(2, person.getAge());
psmt.setString(3, person.getAddress());
psmt.setInt(4, person.getId());

return psmt.executeUpdate()>0;
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(rs, psmt, conn);
}
return false;
}


@Override
public boolean delPerson(int id) {
conn = getConn() ;
String sql = "delete from t_user where id = ? " ;
try {
psmt = conn.prepareStatement(sql);
psmt.setInt(1, id);

return psmt.executeUpdate() > 0 ;
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(rs, psmt, conn);
}

return false;
}


@Override
public List<Person> getPersonList() {
List<Person> personList = new ArrayList<Person>();
conn = getConn() ;
String sql = "select * from t_user" ;
try {
psmt = conn.prepareStatement(sql);
rs = psmt.executeQuery();
while(rs.next()){
int id = rs.getInt(1);
String pname = rs.getString(2);
int age = rs.getInt(3);
String address = rs.getString(4);

Person person = new Person(id, pname, age, address);
personList.add(person);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(rs, psmt, conn);
}
return personList;
}


@Override
public Person getPerson(int id) {
conn = getConn() ;
String sql = "select * from t_user where id = ? " ;
try {
psmt = conn.prepareStatement(sql);
psmt.setInt(1, id);
rs = psmt.executeQuery();
if(rs.next()){
String pname = rs.getString(2);
int age= rs.getInt(3);
String address = rs.getString(4);
return new Person(id , pname , age , address ) ;
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(rs, psmt, conn);
}
return null;
}

 

}

 

Person

public class Person {
private int id;
private String pname;
private int age;
private String address;


public Person() {
}


public Person(String pname, int age, String address) {
super();
this.pname = pname;
this.age = age;
this.address = address;
}


public Person(int id, String pname, int age, String address) {
super();
this.id = id;
this.pname = pname;
this.age = age;
this.address = address;
}


public int getId() {
return id;
}


public void setId(int id) {
this.id = id;
}


public String getPname() {
return pname;
}


public void setPname(String pname) {
this.pname = pname;
}


public int getAge() {
return age;
}


public void setAge(int age) {
this.age = age;
}


public String getAddress() {
return address;
}


public void setAddress(String address) {
this.address = address;
}


@Override
public String toString() {
return "Person [id=" + id + ", pname=" + pname + ", age=" + age
+ ", address=" + address + "]";
}

 

}

 

PersonDAOTest

import com.gem.java.jdbc.dao.PersonDAO;
import com.gem.java.jdbc.dao.impl.PersonDAOImpl;
import com.gem.java.jdbc.pojo.Person;


import junit.framework.TestCase;


public class PersonDAOTest extends TestCase {

PersonDAO personDAO = new PersonDAOImpl();

public void testAddPerson() {
Person person = new Person("小龙女",18,"古墓");
boolean flag = personDAO.addPerson(person);
assertTrue(flag);
}



}