1.测试数据库及表结构
CREATE DATABASE /*!32312 IF NOT EXISTS*/`petinfo` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `petinfo`;
/*Table structure for table `userinfo` */
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(12) NOT NULL,
`password` varchar(12) NOT NULL,
`userphone` varchar(15) default NULL,
PRIMARY KEY (`username`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `userinfo` */
insert into `userinfo`(`id`,`username`,`password`,`userphone`) values (1,'123','123',NULL),(2,'admin','123',NULL),(3,'wang','123f',NULL),(4,'wangkai','123f',NULL);
2.数据库连接通用类
package com.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class ConMysqlDao {
static {
// 加载驱动程序
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("驱动加载失败");
}
}
// 数据库连接字符串
private String url = "jdbc:mysql://localhost:3306/petinfo";
// 用户名
private String userName = "root";
// 密码
private String passWord = "123456";
// 连接对象
public Connection con = null;
// 语句对象
public PreparedStatement ps = null;
// 数据库连接方法
public void prepareConnection() {
try {
if (con == null || con.isClosed()) {
con = DriverManager.getConnection(url, userName, passWord);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException("连接异常:" + e.getMessage());
}
}
// 关闭方法
public void close() {
try {
if (ps != null) {
ps.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException("关闭连接异常:" + e.getMessage());
}
}
// 操作回滚
public void rollback() {
try {
con.rollback();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException("回滚失败:" + e.getMessage());
}
}
}
3.封装实例类
package com.dao;
public class UserBean {
private Integer id;
private String username;
private String password;
private String userphone;
// 空的构造方法
public UserBean() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getUserphone() {
return userphone;
}
public void setUserphone(String userphone) {
this.userphone = userphone;
}
}
4.本封装dao的实例运用,获取数据库中所以用户记录
package com.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class GetAllUser {
ConMysqlDao csd;
// UserBean user = new UserBean();
private String sql = "SELECT * FROM userinfo";
public GetAllUser() {
}
public List<UserBean> allUser() {
List<UserBean> users = new ArrayList<UserBean>();
csd = new ConMysqlDao();
try {
csd.prepareConnection();
csd.ps = csd.con.prepareStatement(sql);
ResultSet rs = csd.ps.executeQuery();
while (rs.next()) {
UserBean user = new UserBean();
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setUserphone(rs.getString("userphone"));
// 将找到的记录添加进users集合中
users.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
csd.close();
}
return users;
}
}
上边类的测试使用:
package com.test;
import java.util.List;
import com.dao.GetAllUser;
import com.dao.UserBean;
public class testallusers {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
List<UserBean> users=new GetAllUser().allUser();
System.out.println("id | name | password | phone");
int i=1;
for(UserBean user : users){
System.out.print(i++);
System.out.print(" | ");
System.out.print(user.getUsername());
System.out.print(" | ");
System.out.print(user.getPassword());
System.out.print(" | ");
System.out.println(user.getUserphone());
}
}
}
总结:
1.连接数据代码写好之后,记得务必把连接驱动jar包,放进去,初学者经常出现这种问题,所以在需要用到连接数据的项目时,首相将jar包放进去再进行,后边工作。
2.本例,思路很简单,充分利用了java的面向对象编程。将需要使用的对象,bean化使用时只需传递对象即可,避免了数据传递获取时无章可循的尴尬
3.将连接类分离出去,在别的数据库连接也是可以使用,做到了代码的重用。
4.数据库连接,使用完毕后务必关闭连接,不然会占用系统资源且不安全。
5.本例还使用了,对于数据库的记录进行物理修改时,若系统出错即可回滚事务