连接数据库简单的spring java项目

环境及工具：Eclipse、mysql8.0

项目成型后的结构以及需要自己引入的jar包：

commons-logging-1.1.1 .jar
spring-aop-4.3.9.RELEASE.jar
spring-beans-4.3.9.RELEASE.jar
spring-context-4.3.9.RELEASE.jar
spring-core-4.3.9.RELEASE.jar
spring-expression-4.3.9.RELEASE.jar
aopalliance-1.O.jar
commons-dbcp-1.4.jar
commons-pool-1.6.jar
ojdbc10-19.8.0.0.jar 
spring-jdbc-4.3.9.RELEASE.jar 
spring-tx-4.3.9.RELEASE.jar 
mysql-connector-java-8.0.16.jar

1、配置文件src/applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd">
		
     <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" >
    	<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" > </property>
    	<property name="url"  		value="jdbc:mysql://127.0.0.1:3306/dbo?useSSL=false&amp;serverTimezone=UTC&amp;&amp;allowPublicKeyRetrieval=true" >  </property>
		<property name="username"  	value="123456" > </property>
		<property name="password"  	value="123456" > </property> 
	
    </bean>

</beans>

2、实体类代码 src/com/entity/Students.java

package com.entity;

public class Students {
	
	private Integer id ;
	private String name ;
	private Integer age ;
	private String no ;
	
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	public String getNo() {
		return no;
	}
	public void setNo(String no) {
		this.no = no;
	}
	
	@Override
	public String toString() {
		return "Students [id=" + id + ", name=" + name + ", age=" + age + ", no=" + no + "]";
	}
}

3、测试用例 src/com/control/Test.java

package com.control;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.entity.Students;



public class Test {

	public static void main(String[] args) {
		objToCls(Students.class);
		String sql = " update Students set name='托尔', age='200', no='00005' where id=2 ";
		clsToUpdate( sql ) ;
		
	}
	
	/**
	 * 查询结果转对象集合
	 * @param <T>
	 * @return 
	 * */ 
	@SuppressWarnings({ "unchecked", "rawtypes", "resource" })
	public static <T> void objToCls(Class<T> cls) {
		try {
			// 数据库配置文件
			ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
			DataSource dataSource = (DataSource) context.getBean("dataSource");
			
			Field[] fields = cls.getDeclaredFields();
			StringBuffer countName = new StringBuffer();
			for (Field field : fields) {
				countName.append(countName.toString().equals("")? "" : ", ").append(field.getName()) ;
			}
			if (countName.toString().equals("")) {
				 System.out.println("传输类无效");
				 return ;
			}
			// 组装查询语句
	        String sql = "select "+ countName +" from "+ cls.getSimpleName();
	        System.out.println("执行语句："+sql);
	        // 建立数据库连接
	        Connection connection = dataSource.getConnection();
	        Statement stm = connection.createStatement();
	        // 执行查询语句，返回结果集
	        ResultSet rs = stm.executeQuery(sql);

	        // 只取数据
	        /* 
	        while (rs.next()) {
	            System.out.println("id=" + rs.getString("id") + " name=" + rs.getString("name") + " age=" + rs.getString("age"));
	        }
	        */
	       
	        // 数据转换成对象
	        List list = new ArrayList();

			ResultSetMetaData metaData = rs.getMetaData(); //获得结果集结构信息,元数据 
			while (rs.next()) {
				//遍历每一列
                //通过反射获取对象的实例
                Object t = cls.newInstance();
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    //获取列的名字
                    String fName = metaData.getColumnLabel(i + 1);
                    for (Field field : fields) {
                        if (fName.equals(field.getName())) {
                            Object obj = rs.getObject(fName);
                            field.setAccessible(true);
	                      //可以根据自己的需求添加几个
                            if (field.getType().getName().equals("java.lang.Integer")) {
                                field.set(t, Integer.parseInt(String.valueOf(obj)));
                            }
                            if (field.getType().getName().equals("java.lang.String")) {
                                field.set(t, String.valueOf(obj).equals("null") ? " ": String.valueOf(obj));
                            }
                            field.setAccessible(false);
                        }
                    }
                }
                //把赋值后的对象 加入到list集合中
                list.add(t);
			}
			
			for (int i = 0; i < list.size(); i++) {
				Students students = (Students) list.get(i) ;
				System.out.println(students.getName()+ "  " + students.getAge() + "  " +students.getNo()  );
			}

	        System.out.println(list);
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	/**
	 * 修改或新增数据返回执行行数， 执行 INSERT、UPDATE 或 DELETE 语句
	 * @param sql 要执行的sql语句
	 * @return 
	 * */ 
	@SuppressWarnings("resource")
	public static void clsToUpdate( String sql ) {
		try {
			// 数据库配置文件
			ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
			DataSource dataSource = (DataSource) context.getBean("dataSource");
	        // 建立数据库连接
	        Connection connection = dataSource.getConnection();
	        Statement stm = connection.createStatement();
	        // 执行查询语句，返回结果集
	        int count = stm.executeUpdate(sql);
	        System.out.println("成功执行"+count+"条语句");
	       
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		
	}

}

4、直接选中main方法，右键，run as  ---> java application 运行即可。