MySql连接及数据的传输例子

1.首先建立数据库

2.导入JDBC，beanutil单元测试包

3.javaBean类(和数据库列相同，最好列名相同)

package com.dp.web.domain;

import java.io.Serializable;
import java.util.Date;

public class Customer implements Serializable {
	private String id ;
	private String name ;
	private String gender ;
	private Date birthday;
	private String cellphone ;
	private String email ;
	private String hobby ;
	private String type ;
	private String description ;
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getCellphone() {
		return cellphone;
	}
	public void setCellphone(String cellphone) {
		this.cellphone = cellphone;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getHobby() {
		return hobby;
	}
	public void setHobby(String hobby) {
		this.hobby = hobby;
	}
	public String getType() {
		return type;
	}
	public void setType(String type) {
		this.type = type;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	
	
}

4.连接数据库的工具文件

    先准备db.properties文件，这个主要是用来保存数据库的连接信息（库名,用户,密码），以便在连接工具来的时候可以多次使用.

DriverclassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day15
user=root
password=root

  建立连接工具类(在这个中就可以读取上面那个文件信息了)

package com.dp.web.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;


public class JdbcUtil {
	
	private static String DriverclassName;
	private static String password;
	private static String url;
	private static String user;
	
	static{
		//读取配置文件，将其中的文件读取出来用
		InputStream in=JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");
		Properties pro=new Properties();
		try {
			pro.load(in);
			DriverclassName=pro.getProperty("DriverclassName");
			password=pro.getProperty("password");
			url=pro.getProperty("url");
			user=pro.getProperty("user");
			
			Class.forName(DriverclassName);//注册一次就可以了
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection() throws Exception{
		 
		return DriverManager.getConnection(url,user, password);
	}
	public static void release(ResultSet rs,Statement stmt,Connection c){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs=null;
		}
		if(stmt!=null){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stmt=null;
		}
		if(c!=null){
			try {
				c.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			c=null;
		}
	}
}

5.dao接口层(增删改查)

package com.dp.web.dao;

import java.util.List;

import com.dp.web.domain.Customer;
import com.dp.web.exception.IdIsNullException;

public interface CustomerDao {
	/**
	 * @param addCustomer
	 * 添加客户信息
	 */
	void addCustomer(Customer c);
	/**
	 * 根据ID删除客户信息
	 * @param customerId
	 */
	void delCustomerById(String customerId);
	/**
	 * 更新客户的信息
	 * 如果更新的客户的ID为null
	 * 则出现异常
	 * @param c
	 * @throws IdIsNullException
	 */
	void updateCustomer(Customer c) throws IdIsNullException;
	/**
	 * @return
	 * 查询客户所有的信息
	 */
	List<Customer> findAll();
	
	/**
	 * 根据客户的id查询客户信息
	 * @param customerId
	 * @return
	 */
	Customer findCustomerById(String customerId);
}

6.dao实现层（增删改查数据处理）

package com.dp.web.dao.iml;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.dp.web.dao.CustomerDao;
import com.dp.web.domain.Customer;
import com.dp.web.exception.DaoException;
import com.dp.web.exception.IdIsNullException;
import com.dp.web.util.JdbcUtil;

public class CustomerDaoImp implements CustomerDao {


	@Override
	public void addCustomer(Customer c) {
		if(c==null)
			throw new IllegalArgumentException();
		Connection conn=null;
		PreparedStatement stam=null;
		ResultSet resu=null;
		
		
		try{
			conn=JdbcUtil.getConnection();
			stam=conn.prepareStatement("insert into customer (id,name,gender,birthday,cellphone,email,hobby,type,description) value(?,?,?,?,?,?,?,?,?)");
			stam.setString(1, c.getId());
			stam.setString(2, c.getName());
			stam.setString(3, c.getGender());
			stam.setDate(4, new java.sql.Date(c.getBirthday().getTime()));
			stam.setString(5, c.getCellphone());
			stam.setString(6, c.getEmail());
			stam.setString(7, c.getHobby());
			stam.setString(8, c.getType());
			stam.setString(9, c.getDescription());
			
			stam.executeUpdate();//更新到数据库
			
		}catch(Exception e){
			throw new DaoException();
		}finally{
			JdbcUtil.release(resu, stam, conn);
		}
	}

	@Override
	public void delCustomerById(String customerId) {
		if(customerId==null)
			throw new IllegalArgumentException();
		Connection conn=null;
		PreparedStatement stam=null;
		ResultSet resu=null;
		
		
		try{
			conn=JdbcUtil.getConnection();
			stam=conn.prepareStatement("delete from customer where id=?");
			stam.setString(1, customerId);
			
			
			stam.executeUpdate();//更新到数据库
			
		}catch(Exception e){
			throw new DaoException();
		}finally{
			JdbcUtil.release(resu, stam, conn);
		}
	}

	@Override
	public void updateCustomer(Customer c) throws IdIsNullException {
		if(c==null) 
			throw new IllegalArgumentException();
		if(c.getId()==null)
			throw new IdIsNullException("this customer's ID cant't null");
		Connection conn=null;
		PreparedStatement stam=null;
		ResultSet resu=null;
		
		
		try{
			conn=JdbcUtil.getConnection();
			stam=conn.prepareStatement("update customer set name=?,gender=?,birthday=?,cellphone=?,email=?,hobby=?,type=?,description=? where id=?");
			
			stam.setString(1, c.getName());
			stam.setString(2, c.getGender());
			stam.setDate(3, new java.sql.Date(c.getBirthday().getTime()));
			stam.setString(4, c.getCellphone());
			stam.setString(5, c.getEmail());
			stam.setString(6, c.getHobby());
			stam.setString(7, c.getType());
			stam.setString(8, c.getDescription());
			stam.setString(9, c.getId());
			
			
			stam.executeUpdate();//更新到数据库
			
		}catch(Exception e){
			throw new DaoException();
		}finally{
			JdbcUtil.release(resu, stam, conn);
		}
	}

	@Override
	public List<Customer> findAll() {
		Connection conn=null;
		PreparedStatement stam=null;
		ResultSet resu=null;
		
		List<Customer> cs=new ArrayList<Customer>();
		try{
			conn=JdbcUtil.getConnection();
			stam=conn.prepareStatement("select id,name,gender,birthday,cellphone,email,hobby,type,description from customer");
			resu=stam.executeQuery();
			
			while(resu.next()){
				Customer c=new Customer();
				c.setId(resu.getString("id"));
				c.setName(resu.getString("name"));
				c.setGender(resu.getString("gender"));
				c.setBirthday(resu.getDate("birthday"));
				c.setCellphone(resu.getString("cellphone"));
				c.setEmail(resu.getString("email"));
				c.setHobby(resu.getString("hobby"));
				c.setType(resu.getString("type"));
				c.setDescription(resu.getString("description"));
				
				cs.add(c);
			}
			return cs;
		}catch(Exception e){
			throw new DaoException();
		}finally{
			JdbcUtil.release(resu, stam, conn);
		}
		
	}

	@Override
	public Customer findCustomerById(String customerId) {
		if(customerId==null)
			throw new IllegalArgumentException();
		Connection conn=null;
		PreparedStatement stam=null;
		ResultSet resu=null;
		
		try{
			conn=JdbcUtil.getConnection();
			stam=conn.prepareStatement("select id,name,gender,birthday,cellphone,email,hobby,type,description from customer where id=?");
			stam.setString(1, customerId);
			
			resu=stam.executeQuery();
			
			if(resu.next()){
				Customer c=new Customer();
				c.setId(resu.getString("id"));
				c.setName(resu.getString("name"));
				c.setGender(resu.getString("gender"));
				c.setBirthday(resu.getDate("birthday"));
				c.setCellphone(resu.getString("cellphone"));
				c.setEmail(resu.getString("email"));
				c.setHobby(resu.getString("hobby"));
				c.setType(resu.getString("type"));
				c.setDescription(resu.getString("description"));
				return c;
			}
			return null;
		}catch(Exception e){
			throw new DaoException();
		}finally{
			JdbcUtil.release(resu, stam, conn);
		}
		
	}

}

测试层：(开发中不需要这个，这个只是用来测试的)

package com.dp.web.test;

import static org.junit.Assert.*;

import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.UUID;

import org.junit.Test;

import com.dp.web.dao.CustomerDao;
import com.dp.web.dao.iml.CustomerDaoImp;
import com.dp.web.domain.Customer;
import com.dp.web.exception.IdIsNullException;

/**
 * @author Administrator
 * Customer访问数据库的单元测试/只是用来测试是否正确的
 *
 */
public class CustomerDaoImpTest {
	
	private CustomerDao dao=new CustomerDaoImp();
	
	
	//id,name,gender,birthday,cellphone,email,hobby,type,description
	@Test
	public void testAddCustomer() {
		Customer c=new Customer();
		//加数据
//		c.setId(UUID.randomUUID().toString());
		c.setId("1");
		c.setName("Mr_Li13");
		c.setGender("0");//0代表男人
		c.setBirthday(new Date());
		c.setCellphone("55555686");
		c.setEmail("45862@qq.com");
		c.setHobby("网球");
		c.setType("VIP");
		c.setDescription("他是男人");
		//将对象放到dao接口实现的对象中
		dao.addCustomer(c);
	}

	@Test
	public void testDelCustomerById() {
		dao.delCustomerById("143a380a-8a4a-486f-9e9d-070bfe616b6e");
	}

	@Test(expected=com.dp.web.exception.IdIsNullException.class)
	public void testUpdateCustomer() throws IdIsNullException {
		Customer c=new Customer();
		dao.updateCustomer(c);
		//如果显示绿色说明测试异常
		//下面那个测试才是update的测试
	}
	
	@Test
	public void testUpdateCustomer1() throws IdIsNullException{
		Customer c=dao.findCustomerById("143a380a-8a4a-486f-9e9d-070bfe616b6e");
		c.setGender("0");
		dao.updateCustomer(c);
	}

	@Test
	public void testFindAll() {
		List<Customer> list1=dao.findAll();
		assertEquals(1, list1.size());
//		for(int i=0;i<list1.size();i++){
//			System.out.println(list1.get(i));
//		}
//		Iterator t=list1.iterator();
//		while(t.hasNext()){
//			System.out.println(t.next());
//		}
//		for(Object s:list1){
//			System.out.println(s);
//		}
	}

	@Test
	public void testFindCustomerById() {
		Customer c=dao.findCustomerById("143a380a-8a4a-486f-9e9d-070bfe616b6e");
		assertNotNull(c);
		//上面这个是测试有结果的，下面这个是测试没有结果的
		c=dao.findCustomerById("2");
		assertNull(c);
	}

}

这就是写数据库到dao的过程，如果写mvc的话，service接口层的方法可以暂时和dao接口层的方法相同，因为规则是这样的，后续的可以慢慢添加。

在我的资源中有一个资源可以共享，主要是用户信息封装到数据库，采用mvc结构+三层架构。

连接：http://download.csdn.net/detail/mr_li13/9191051