mysql数据库连接简单分析 dao的应用

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 blocke.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 blocke.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 blocke.printStackTrace();throw new RuntimeException("关闭连接异常:" + e.getMessage());}}// 操作回滚public void rollback() {try {con.rollback();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blocke.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 stubList<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.本例还使用了，对于数据库的记录进行物理修改时，若系统出错即可回滚事务