Java使用jdbc连接MySQL数据库详细实例

MySQL数据库：

首先，我们需要在Mysql的test数据库下新建一个table，比如如下student表：

该表的create语句如下：

CREATE TABLE `student` (  `sno` int(11) NOT NULL,  `sname` varchar(20) NOT NULL,  `sex` varchar(1) DEFAULT NULL,  `birthday` date DEFAULT NULL,  `email` varchar(20) DEFAULT NULL,  `addr` varchar(20) DEFAULT NULL,  PRIMARY KEY (`sno`)) 

注意：请在操作之前自行插入测试数据。

Java代码：

0.    添加MySQL驱动jar到项目路径（点此下载驱动jar）

1.    在org.plyy.utils包下建DBManager类，该类的作用是管理新建和关闭数据库连接：

package org.plyy.utils;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class DBManager {private static Connection conn = null;private static final String DB_USER = "root";//数据库用户名private static final String DB_PASS = "test123";//用户密码private static final String DB_URL = "jdbc:mysql://localhost:3306/test";//数据库对应urlprivate static final String DB_DRIVER = "com.mysql.jdbc.Driver";//数据库驱动        /*建立数据库连接，返回连接对象*/public static Connection getConnection(){try {Class.forName(DB_DRIVER);//装载数据库驱动程序System.out.println("Success loading Mysql Driver!");conn = DriverManager.getConnection(DB_URL,DB_USER,DB_PASS);//建立连接，获取连接对象System.out.println("Success connect Mysql server!");} catch (ClassNotFoundException e) {System.out.println("Error loading Mysql Driver!");e.printStackTrace();} catch (SQLException e) {System.out.println("Error connect Mysql server!");e.printStackTrace();}return conn;}/*关闭连接*/public static void closeAll(Connection conn, PreparedStatement stmt, ResultSet rs){try {if(rs != null){rs.close();}if(stmt != null){stmt.close();}if(conn != null){conn.close();}} catch (SQLException e) {System.out.println("Error close connection!");e.printStackTrace();}}}

2.    在org.plyy.entity包下建Student类，该类的作用是关联数据库中的student表，将数据库的数据对应到Java实体：

package org.plyy.entity;import java.sql.Date;public class Student {private int sno;privateString sname;privatechar sex;privateDate birthday;private String email;private String addr;public Student(int sno, String sname, char sex, Date birthday,String email, String addr) {super();this.sno = sno;this.sname = sname;this.sex = sex;this.birthday = birthday;this.email = email;this.addr = addr;}@Overridepublic String toString() {return  sno + "\t" + sname + "\t" + sex+ "\t" + birthday + "\t" + email + "\t"+ addr;}public int getSno() {return sno;}public void setSno(int sno) {this.sno = sno;}public String getSname() {return sname;}public void setSname(String sname) {this.sname = sname;}public char getSex() {return sex;}public void setSex(char sex) {this.sex = sex;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public String getAddr() {return addr;}public void setAddr(String addr) {this.addr = addr;}}

3.    在org.plyy.dao包下建IStudentDao接口，该接口的作用是定义各种数据操作方法：

package org.plyy.dao;import java.util.List;import org.plyy.entity.Student;public interface IStudentDao {List<Student> getAllStudent();//获得student表中的数据}

4.    在org.plyy.dao.impl包下建StudentDaoImpl类，该类的作用是实现dao下定义的各种数据操作方法：

package org.plyy.dao.impl;import java.sql.Connection;import java.sql.Date;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import org.plyy.dao.IStudentDao;import org.plyy.entity.Student;import org.plyy.utils.DBManager;public class StudentDaoImpl implements IStudentDao{private Connection conn = null;private PreparedStatement ptst = null;private ResultSet rs = null;@Overridepublic List<Student> getAllStudent() {List<Student> list = new ArrayList<Student>(); conn = DBManager.getConnection();String sql = "select * from student";try {ptst = conn.prepareStatement(sql);rs = ptst.executeQuery();Student stu = null;while(rs.next()){int sno = rs.getInt("sno");String sname = rs.getString("sname");char sex = rs.getString("sex").charAt(0);Date birthday = rs.getDate("birthday");String email = rs.getString("email");String addr = rs.getString("addr");stu = new Student(sno,sname,sex,birthday,email,addr);list.add(stu);}} catch (SQLException e) {System.out.println("Error execute sql statement!");e.printStackTrace();}finally{DBManager.closeAll(conn,ptst,rs);}return list;}

5.    在org.plyy.test包下建test类，完成测试：

package org.plyy.test;import java.util.ArrayList;import java.util.List;import org.plyy.dao.IStudentDao;import org.plyy.dao.impl.StudentDaoImpl;import org.plyy.entity.Student;public class Test {public static void main(String[] args) {List<Student>  l_test = new ArrayList<Student>();IStudentDao test = new StudentDaoImpl();                l_test = test.getAllStudent();System.out.println("sno\tsname\t\tsex\tbirthday\temail\t\taddr");for (Student stu : l_test) {System.out.println(stu);}}}

至此结束。

有任何问题请咨询王萌([e-mail])