SpringMVC（8）：使用JDBC+springmvc 完成useDao的查询和添加用户（小实验）

18/1/9

     jdbc的操作非常简单，springmvc 最初也是直接跟jdbc打交道的。因此，我也做了个小Demo，要求如下：

1、给订单系统增加ConfigManager.java 工具类，来完成数据库配制文件的读取。该类设计为单例，考虑线程；

2、编写Junit测试类，测试查询用户和增加用户信息。

【0】文件结构：

结果是单例的ConfigManager类出现了空指针异常，我会后续跟进。

【1】新建/database.properties文件：

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test/smbms_user
jdbc.username=root
jdbc.password=

【2】新建ConfigManager.java 文件（静态内部类的使用）：

package file.InnerClass;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;


public class ConfigManager {
	private static ConfigManager singleton;
	private static Properties properties;
	private ConfigManager(){
		//可以添加业务代码--整个系统运行只会执行一次的业务代码操作
		String configFile = "database.properties";
		properties = new Properties();
//		ConfigManager.class.getClassLoader();
//		InputStream is = ClassLoader.getSystemResourceAsStream(configFile);
		InputStream is = ConfigManager.class.getClassLoader().getResourceAsStream(configFile);
		try {
			properties.load(is);
			is.close();
		}catch(IOException e){
			e.printStackTrace();
		}
	}
	
	//Inner class
	public static class ConfigManagerHelper{
		private static final ConfigManager INSTANCE = new ConfigManager();
	}
	
	public static ConfigManager getInstance(){
		singleton = ConfigManagerHelper.INSTANCE;
		return singleton;
	}
	
	public static String getValue(String key){
		//API
		return properties.getProperty(key);
	}
}

【3】新建BaseDao.java文件：

package file.InnerClass;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.smbms.entities.User;

public class BaseDao {
	public static Connection getConnection(){
		Connection connection = null;
		ConfigManager.getInstance();
		String driver = ConfigManager.getValue("driver");
		ConfigManager.getInstance();
		String url = ConfigManager.getValue("url");
		ConfigManager.getInstance();
		String username = ConfigManager.getValue("username");
		ConfigManager.getInstance();
		String password = ConfigManager.getValue("password");
		try{
			Class.forName(driver);
			connection = DriverManager.getConnection(url,username,password);
			
		}catch(Exception e){
			e.getMessage();
		}
		return connection;
	}
	
	//连接得到statement
	public static Statement getStatement(Connection conn) throws SQLException{
		return conn.createStatement();
	}

	//返回一个user对象
	public static ArrayList<User> getUseList(ResultSet rs) throws SQLException{
		ArrayList<User> userList = new ArrayList<User>();
		User user = new User();
		while(rs.next()){
			user.setId(rs.getInt(0));
			user.setUserCode(rs.getString(1));
			user.setUserName(rs.getString(2));
			user.setUserPassword(rs.getString(3));
			user.setGender(rs.getInt(4));
			user.setBirthday(rs.getDate(5));
			user.setPhone(rs.getNString(6));
			user.setAddress(rs.getNString(7));
			user.setUserRole(rs.getInt(8));
			user.setCreatedBy(rs.getInt(9));
			user.setCreationDate(rs.getDate(10));
			userList.add(user);
		}
		return userList;
	}
	
	//添加一个user对象
	public static boolean addUser(String sql){
		boolean result =false;
		try {
			BaseDao.getConnection().createStatement().executeQuery(sql);
			result = true;
		} catch (SQLException e) {
			result =false;
			e.printStackTrace();
		}
		return result;
	}
}

【4】新建UserDao.java 文件：

package com.smbms.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.smbms.entities.User;

import file.InnerClass.BaseDao;

public  class UserDao {
	public static ArrayList<User> allUserShow() throws SQLException{
		String sql = "select * from smbms_user";
//		ArrayList<User> allUser = new ArrayList<User>();
		System.out.println(sql);
		ResultSet rs = BaseDao.getConnection().createStatement().executeQuery(sql);
		return BaseDao.getUseList(rs);
	}
	
	public static boolean addNewUser(User user){
		boolean result =false;
		String sql = "insert into smbms_user"
				+ "(userCode,userName,userPassword,gender,birthday,phone,address,userRole,createBy,createDate)"
				+ "values"
				+ "("+user.getUserCode()+","+user.getUserName()+","+
				user.getUserPassword()+","+user.getGender()+","+
				user.getBirthday()+","+user.getPhone()+","+user.getAddress()+","+
				user.getUserRole()+","+user.getCreatedBy()+","+user.getCreationDate()+");";
		System.out.println("***"+sql);
		result = BaseDao.addUser(sql);
		return result;
	}
}

解释：其实就是在BaseDao的基础上，再进行了一次封装。

【5】写个单元测试类：

package com.smbms.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import org.junit.Test;

import com.smbms.entities.User;

public class UserDaoTest {

	@Test
	public void test() throws SQLException {
		UserDao userDao = new UserDao();
		User user = new User();
		user.setUserName("leilaohu");
		System.out.println("addusertest result: "+userDao.addNewUser(user));
		System.out.println("-==============");
		ArrayList<User> userList = new ArrayList<User>();
		userList = userDao.allUserShow();
		for(User u:userList){
			System.out.println(u.toString());
		}
		
	}

}

由于一个Bug还没解决，所以，之后会继续更新。

Log如下：

java.lang.NullPointerException
	at file.InnerClass.BaseDao.addUser(BaseDao.java:63)
	at com.smbms.dao.UserDao.addNewUser(UserDao.java:30)
	at com.smbms.dao.UserDaoTest.test(UserDaoTest.java:17)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)