SpringJdbc查询，_spring jdbc 带套件查询

SpringJdbc文档


新建一个项目，构架一个Spring，勾选前三个架包。
在项目上新建一个file。起名为：jdbc.properties.


在这个里面直接写：这里以Oracle数据库weili
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:orcl
user=system     //这里如果起名usename会报错，貌似这是个关键字。名字可以随便起在配置文件里可以映射
pwd=password




进入配置applicationContext.xml文件里
配置Context的命名空间


<!--加载指定属性 文件（jdbc.properties） -->
<!--定义DataSource组件 -->




配置完毕后。新建一个类User
新建UserRowMapperl类，这个主要用来查询一个表所有的数据。要实现RowMapper接口


新建一个UserDao要继承IdbcDaoSupport，这样更方便，可以直接得到getJdbcTemple（）这个数据模板。直接调用的方法




继续在配置文件applicationContext配置JdbcTemplat模板 组件

<!--定义Dao组件,这个UserDao继承了JdbcDaoSupport里面会有 jdbcTemplate这个属性 -->

jdbc.properties

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:orcl
user=system
pwd=password

User类

package entity;
 
import java.util.Date;
 
public class User {
	private Integer id;
	private String username;
	private String usepwd;
	private String nickname;
	private Integer age;
	private Date date;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getUsepwd() {
		return usepwd;
	}
	public void setUsepwd(String usepwd) {
		this.usepwd = usepwd;
	}
	public String getNickname() {
		return nickname;
	}
	public void setNickname(String nickname) {
		this.nickname = nickname;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	public Date getDate() {
		return date;
	}
	public void setDate(Date date) {
		this.date = date;
	}
	public User(Integer id, String username, String usepwd, String nickname,
			Integer age, Date date) {
		super();
		this.id = id;
		this.username = username;
		this.usepwd = usepwd;
		this.nickname = nickname;
		this.age = age;
		this.date = date;
	}
	public User() {
		super();
		// TODO Auto-generated constructor stub
	}
	 
	
	
 
}

UserRowMapper

package entity;
 
import java.sql.ResultSet;
import java.sql.SQLException;
 
import org.springframework.jdbc.core.RowMapper;
 
public class UserRowMapper implements RowMapper<User>{
 
	@Override
	public User mapRow(ResultSet rs, int arg1) throws SQLException {
 
		return new User(rs.getInt("id"),rs.getString("username"),rs.getString("userpwd"),rs.getString("nickname"),rs.getInt("age"),rs.getDate("birthday"));
	}
 
}

UserDao

package dao;
 
import java.util.List;
 
import org.springframework.jdbc.core.support.JdbcDaoSupport;
 
import entity.User;
import entity.UserRowMapper;
 
public class UserDao extends JdbcDaoSupport {
	/**
	 * 查询所有用户
	 */
 
	public List<User> getAllUser() {//这个UserMapper是提前定义好的
		return getJdbcTemplate().query("select * from myuser",new UserRowMapper());
				
 
	}
 
}

Test

import java.util.List;
 
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
 
import dao.UserDao;
import entity.User;
 
 
public class Test {
public static void main(String[] args) {
	//获取AC容器
	ApplicationContext ac=new ClassPathXmlApplicationContext("applicationContext.xml");
	   UserDao bean = (UserDao) ac.getBean("userDao");
	   List<User> allUser = bean.getAllUser();
	   
	   for (User user : allUser) {
		System.out.println(user.getId()+"姓名为："+user.getUsername()+"昵称为："+user.getNickname()+"密码为："+user.getUsepwd()+"年龄为："+user.getAge()+"生日为:"+user.getDate());
	}
	
	
	
}
}

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:p="http://www.springframework.org/schema/p"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans 
	http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
	http://www.springframework.org/schema/context 
	http://www.springframework.org/schema/context/spring-context-3.1.xsd">
	
<!--加载指定属性 文件（jdbc.properties） -->
<context:property-placeholder location="classpath:jdbc.properties"/>
<!--定义DataSource组件 ,貌似好几个组件-->
<!-- <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"></bean> -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName" value="${driver}"></property>
  <property name="url" value="${url}"></property>
  <property name="username" value="${user}"></property>
  <property name="password" value="${pwd}"></property>
</bean>
 
<!--定义JdbcTemplate模板组件  --><!-- ctrl+t搜索 JdbcTemplate出现这个类-->
<bean id="jdbcTemple" class="org.springframework.jdbc.core.JdbcTemplate ">
<property name="dataSource" ref="dataSource"></property>
</bean>
 
<!--定义Dao组件,这个UserDao继承了JdbcDaoSupport里面会有 jdbcTemplate这个属性 -->
<bean id="userDao" class="dao.UserDao">
<property name="jdbcTemplate" ref="jdbcTemple"></property>
</bean>
 
 
 
 
</beans>

增删改查其他的

package dao;
 
import java.util.List;
 
import org.springframework.jdbc.core.support.JdbcDaoSupport;
 
import entity.User;
import entity.UserRowMapper;
 
public class UserDao extends JdbcDaoSupport {
	/**
	 * 查询所有用户
	 */
 
	public List<User> getAllUser() {//这个UserMapper是提前定义好的
		return getJdbcTemplate().query("select * from myuser",new UserRowMapper());
	}
	
	/*
	 * 根据用户名和密码查询指定用户信息
	 */
	
	public User getUserByNameAndPwd(User user){
		String sql="select *  from myuser where username=? and userpwd=?";
		Object[] params={user.getUsername(),user.getUsepwd()};
		return getJdbcTemplate().queryForObject(sql, params,new UserRowMapper());
 
	}
	
	/*
	 * 按用户模糊查询
	 */
	public List<User>getUserLikeName(String name){
		String sql="select * from myuser where username like ?";
		Object[]params={"%"+name+"%"};
		return getJdbcTemplate().query(sql,params,new UserRowMapper());
 
	}
	
	
	/*
	 * 查询用户个数
	 */
	public int FindCount(){
		String sql="select count(*) from myuser";
		return  getJdbcTemplate().queryForInt(sql);	
	}
	
	/*
	 * 新增用户
	 */
	public int addUser(User user){
		String sql="insert into myuser values(seq_mysuser.nextval,?,?,?,?,?)";
	    Object[] params={user.getUsername(),user.getUsepwd(),user.getNickname(),user.getAge(),user.getDate()};
        return getJdbcTemplate().update(sql,params);
 
	}
	
	
	
	
	
	
 
}

import java.util.Date;
import java.util.List;
 
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
 
import dao.UserDao;
import entity.User;
 
public class Test {
	public static void main(String[] args) {
		// 获取AC容器
		ApplicationContext ac = new ClassPathXmlApplicationContext(
				"applicationContext.xml");
		UserDao bean = (UserDao) ac.getBean("userDao");
		List<User> allUser = bean.getAllUser();
 
		for (User user : allUser) {
			System.out.println(user.getId() + "姓名为：" + user.getUsername()
					+ "昵称为：" + user.getNickname() + "密码为：" + user.getUsepwd()
					+ "年龄为：" + user.getAge() + "生日为:" + user.getDate());
		}
 
		// 根据用户名和密码查询
		String name = "02";
		String pwd = "66";
 
		User user = new User();
		user.setUsername(name);
		user.setUsepwd(pwd);
		// 如果没查到该用户，会报异常。所以这里要抛出一下异常
		try {
			user = bean.getUserByNameAndPwd(user);
			if (user != null) {
				System.out.println("存在用户名为：" + user.getUsername() + "的用户");
			}
		} catch (Exception e) {
			System.out.println("该用户不存在");
		}
 
		// 模糊查询
		List<User> users = bean.getUserLikeName("02");
		for (User user2 : users) {
			System.out.println("用户名昵称是：" + user2.getUsername() + "用户名是："
					+ user2.getUsepwd() + "用户昵称是：" + user2.getAge());
		}
		// 查询用户个数
		int count = bean.FindCount();
		System.out.println("用户总个数为：" + count);
 
		//新增用户
		User user3=new User(null,"孟振","12345","小梦",18,new Date());
		if (bean.addUser(user3)>0) {
			System.out.println("新增用户成功！");
		}
		
		
		
	}
}