1.DBCP
a.定义：DBCP（DataBase Connection Pool）数据库连接池，是java数据库连接池的一种，由Apache开发，通过数据库连接池，可以让程序自动管理数据库连接的释放和断开
b.配置：
<bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/emp" />
<property name="username" value="root" />
<property name="password" value="admin"/>
<property name="maxActive" value="10"></property>
<property name="initialSize" value="2"></property>
<property name="minIdle" value="2"></property>
<property name="maxIdle" value="3"></property>
</bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" />
</bean>
c.使用：
@Repository
public class UserDao {
@Resource
private JdbcTemplate template; //增
public void addUser(String name,String pwd){
int userType=1;
String sql="insert into users(user_name,user_pwd,user_type) values(?,?,?)";
jdbcTemplate.update(sql,new Object[]{name,pwd,userType});
} //删
public void delUserById(int id){
String sql="delete from users where user_id=?";
jdbcTemplate.update(sql,new Object[]{id});
} //改
public void updateUser(User user){
String sql="update users set user_name=?,user_pwd=?,user_type=? where user_id=?";
jdbcTemplate.update(sql,new Object[]{user.getUserName(),user.getUserPwd(),user.getUserType(),user.getUserId()});
} //查——方法一
public List<User> findAllUser(){
String sql="select * from users";
List<User> list=jdbcTemplate.query(sql,new BeanPropertyRowMapper(User.class));
return list;
} //查——方法二
public List<User> getAllUser(){
String sql="select * from users";
List<User> list=jdbcTemplate.query(sql,new RowMapper(){ @Override
public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
User u=new User();
if(arg0!=null){
u.setUserId(arg0.getInt("user_id"));
u.setUserName(arg0.getString("user_name"));
u.setUserPwd(arg0.getString("user_pwd"));
u.setUserId(arg0.getInt("user_type"));
}
return u;
} });
return list;
} //多表联查
public List<User> getAllUserAndInfo(){
String sql="select u.user_name,u.user_type,i.info_nickName,i.info_email from users u left join userInfo i on u.user_id=i.user_id ";
List<User> list=jdbcTemplate.query(sql,new RowMapper(){ @Override
public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
User u=new User();
if(arg0!=null){
u.setUserName(arg0.getString("user_name"));
u.setUserType(arg0.getInt("user_type"));
UserInfo info=new UserInfo();
info.setInfoEmail(arg0.getString("info_email"));
info.setInfoNickName(arg0.getString("info_nickName"));
u.setInfo(info);
}
return u;
} });
return list;
} }