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";//数据库对应url
	private 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;
	private	String sname;
	private	char sex;
	private	Date 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;
	}
	
	
	@Override
	public 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;
	
	@Override
	public 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);
		}
	}

}

至此结束。

有任何问题请咨询王萌(puliuyinyi@qq.com)