MySql接続およびデータの転送例

11651 ワード

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("[email protected]");
		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を書くとサービスインタフェース層の方法はdaoインタフェース層の方法と一時的に同じであることができる.ルールはこのようなものであるため、後続はゆっくり追加することができる.
私のリソースには共有できるリソースがあります.主にユーザー情報をデータベースにカプセル化し、mvc構造+3階層アーキテクチャを採用しています.
接続:http://download.csdn.net/detail/mr_li13/9191051

udp socket recvfrom関数について、アプリケーションが指定した受信長さが足りない場合はどうしますか?

手書きMini版Tomcatサーバ