使用Eclipse或MyEclipse连接访问数据库MySql的方法相同。
第一步:注册、连接
public final class JdbcUtils
{
private static
String url = "jdbc:mysql://localhost/jdbc";
private static String user = "root";
private static String password =
"123456";
private
JdbcUtils() {
}// 私有的构造方法,使该类不能在其他地方被实例化
static {//
静态代码块,类被加载的时候静态代码块被加载
try {
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException
e) {
e.printStackTrace();
}
}
// 获得连接
public static Connection getConnection()
{
Connection
conn = null;
try {
conn =
DriverManager.getConnection(url, user, password);
} catch (SQLException e)
{
e.printStackTrace();
}
return
conn;
}
// 释放资源
public static void free(ResultSet rs, Statement
stmt, Connection conn) {
try {
if (rs !=
null) {
rs.close();
}
} catch (SQLException e)
{
e.printStackTrace();
} finally {
try {
if
(stmt != null) {
stmt.close();
}
} catch
(SQLException e) {
e.printStackTrace();
} finally
{
try
{
if
(conn != null) {
conn.close();
}
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
第二步:创建实体类domain
public class User {
private int
id;
private String name;
private Date birthday;
private double money;
public int
getId() {
return id;
}
public void
setId(int id) {
this.id = id;
}
public String
getName() {
return name;
}
public void
setName(String name) {
this.name = name;
}
public Date
getBirthday() {
return birthday;
}
public void
setBirthday(Date birthday) {
this.birthday = birthday;
}
public double
getMoney() {
return money;
}
public void
setMoney(double money) {
this.money = money;
}
public String
toString() {
return "学号="
+ id + "\t姓名=" + name + "\t生日=" + birthday + "\t工资="
+
money;
}
}
第三步:创建domain类的Dao接口
public interface UserDao
{
ArrayList
getAllUsers(); User getUserById(int id);
void create(User
u);
void update(User
u);
void delete(User
u);
}
第四步:编写Dao接口的实现类
public class UserDaoJdbcImpl
implements UserDao {
public void
create(User u) {
Connection
conn = null;
PreparedStatement ps =
null;
ResultSet rs = null;
try {
conn =
JdbcUtils.getConnection();
String
sql = "insert into user (name, birthday, money) values (?, ?,
?)";
ps
= conn.prepareStatement(sql);
ps.setString(1,
u.getName());
ps.setDate(2,
new java.sql.Date(u.getBirthday().getTime()));
ps.setDouble(3,
u.getMoney());
int
i = ps.executeUpdate();
System.out.println("成功向user表中插入"
+ i + "条记录");
} catch (SQLException e)
{
e.printStackTrace();
} finally {
JdbcUtils.free(rs,
ps, conn);
}
}
public void
delete(User u) {
Connection
conn = null;
PreparedStatement ps =
null;
ResultSet rs = null;
try {
conn =
JdbcUtils.getConnection();
String
sql = "delete from user where name = ?";
ps
= conn.prepareStatement(sql);
ps.setString(1,
u.getName());
int
i = ps.executeUpdate();
System.out.println("成功向user表中删除"
+ i + "条记录");
} catch (SQLException e)
{
e.printStackTrace();
} finally {
JdbcUtils.free(rs,
ps, conn);
}
}
public ArrayList
getAllUsers() {
Connection
conn = null;
PreparedStatement ps =
null;
ResultSet rs = null;
ArrayList
allUsers = new ArrayList();
User user = null;
try {
conn =
JdbcUtils.getConnection();
String
sql = "select * from user";
ps
= conn.prepareStatement(sql);
rs
= ps.executeQuery();
while
(rs.next()) {
user
= new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setBirthday(rs.getDate("birthday"));
user.setMoney(rs.getDouble("money"));
allUsers.add(user);
}
} catch (SQLException e)
{
e.printStackTrace();
} finally {
JdbcUtils.free(rs,
ps, conn);
}
return allUsers;
}
public void
update(User u) {
Connection
conn = null;
PreparedStatement ps =
null;
ResultSet rs = null;
try {
conn =
JdbcUtils.getConnection();
String
sql = "update user set name = ?, birthday = ?, money = ? where
id=?";
ps
= conn.prepareStatement(sql);
User user =
getUserById(u.getId());
if
(u.getName() == null) {
u.setName(user.getName());
}
if
(u.getBirthday() == null) {
u.setBirthday(user.getBirthday());
}
if
(u.getMoney() == 0) {
u.setMoney(user.getMoney());
}
ps.setString(1,
u.getName());
ps.setDate(2,
new java.sql.Date(u.getBirthday().getTime()));
ps.setDouble(3,
u.getMoney());
ps.setInt(4,
u.getId());
int
i = ps.executeUpdate();
System.out.println("成功向user表中更新"
+ i + "条记录");
} catch (SQLException e)
{
e.printStackTrace();
} finally {
JdbcUtils.free(rs,
ps, conn);
}
}
public User
getUserById(int id) {
Connection
conn = null;
PreparedStatement ps =
null;
ResultSet rs = null;
User user =
null;
try {
conn =
JdbcUtils.getConnection();
String
sql = "select * from user where id = ?";
ps
= conn.prepareStatement(sql);
ps.setInt(1,
id);
rs
= ps.executeQuery();
if
(rs.next()) {
user
= new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setBirthday(rs.getDate("birthday"));
user.setMoney(rs.getDouble("money"));
}
} catch (SQLException e)
{
e.printStackTrace();
} finally {
JdbcUtils.free(rs,
ps, conn);
}
return
user;
}
}
第五步:编写Dao测试类
public class UserDaoTest
{
public static
void main(String[] args) {
//
create();
//
update();
//
delete();
//
getAllUsers();
getUserById();
}
static void
getAllUsers() {
UserDao ud =
new UserDaoJdbcImpl();
ArrayList
allUsers = ud.getAllUsers();
for (User u :
allUsers) {
System.out.println(u);
}
}
static void
create() {
UserDao ud =
new UserDaoJdbcImpl();
User user =
new User();
user.setName("老师");
Date d =
null;
try {
SimpleDateFormat
sdf = new SimpleDateFormat("yyyy-MM-dd");
d =
sdf.parse("1999-2-3");
} catch (ParseException e)
{
e.printStackTrace();
}
user.setBirthday(d);
user.setMoney(123);
ud.create(user);
}
static void
update() {
UserDao ud =
new UserDaoJdbcImpl();
User user =
new User();
user.setId(9);
user.setName("老师");
Date d =
null;
try {
SimpleDateFormat
sdf = new SimpleDateFormat("yyyy-MM-dd");
d =
sdf.parse("1999-9-14");
} catch (ParseException e)
{
e.printStackTrace();
}
user.setBirthday(d);
user.setMoney(1234);
ud.update(user);
}
static void
delete() {
UserDao ud =
new UserDaoJdbcImpl();
User user =
new User();
user.setName("老师");
ud.delete(user);
}
static void
getUserById() {
UserDao ud =
new UserDaoJdbcImpl();
User user =
ud.getUserById(9);
System.out.println(user);
}
}