JDBC(一 JDBC的基本操作CRUD)

一、JDBC

SpringJDBC 是Spring提供的持久层技术

二、配置JDBC环境

1、导入Spring核心开发包

Spring-bean
Spring-context
Spring-core
Spring-expression

2、导入Commons-logging日志包

commons-logging.

3、导入JDBC模板开发包

spring-jdbc
spring-tx

三、JDBC 执行CRUD

package com.chen.jdbctemplete;

public class User {
    private String name;
    private String gender;
    private int age;
    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 int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }

}

package com.chen.jdbctemplete;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class MyRowMapper implements RowMapper<User>{

    @Override
    public User mapRow(ResultSet arg0, int arg1) throws SQLException {
        //1、从结果集里把数据取出
        String name = arg0.getString("name");
        String gender = arg0.getString("gender");
        int age = arg0.getInt("age");

        User user = new User();
        user.setName(name);
        user.setGender(gender);
        user.setAge(age);

        return user;
    }

}

package com.chen.jdbctemplete;

import java.util.List;

import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

/**
 * jdbcTemplete Demo
 * @author chen
 *
 */
public class JdbcDemo1 {


    public static void main(String[] args) {
        create();
//      insert();
//      update();
//      delete();
//      query();
//      queryAll();
        querySingle();
    }
    //创建表操作
    @Test
    public static void create() {
        //创建表
        String SQL_CREATE_TABLE = "create table if not exists jdbcTable1 (name varchar(10), "
                + "gender varchar(10), "
                + "age int) charset utf8";
        JdbcTemplate jdbcTemplete = getJDBCTemplete();
        int update = jdbcTemplete.update(SQL_CREATE_TABLE); 
    }
    //添加操作
    @Test
    public static void insert() {
        //插入数据
        String SQL_INSERT = "insert into jdbcTable values('lili', 'girl', 18)";
        JdbcTemplate jdbcTemplete = getJDBCTemplete();
        int update = jdbcTemplete.update(SQL_INSERT);
    }

    //修改操作
    @Test
    public static void update() {
        //修改数据
        String SQL_UPDATE = "update jdbcTable set name = ? where age=?";
        JdbcTemplate jdbcTemplete = getJDBCTemplete();
        int update = jdbcTemplete.update(SQL_UPDATE, "wanna", "18");
    }

    //删除操作
    @Test
    public static void delete() {
        //删除数据
        String SQL_DELETE = "delete from jdbcTable where gender = ?";
        JdbcTemplate jdbcTemplete = getJDBCTemplete();
        int update = jdbcTemplete.update(SQL_DELETE, "girl");
    }

    public static void querySingle(){
        //查询数据
        String SQL_QUERY = "select age from jdbctable";
        JdbcTemplate jdbcTemplete = getJDBCTemplete();
        int age = jdbcTemplete.queryForObject(SQL_QUERY, Integer.class, "wanna");
        System.out.println("age: " + age);
    }
    //查询操作
    @Test
    public static void query() {
        //查询数据
        String SQL_QUERY = "select * from jdbctable where name= ?";
        JdbcTemplate jdbcTemplete = getJDBCTemplete();
        User user = jdbcTemplete.queryForObject(SQL_QUERY, new MyRowMapper(), "wanna");
        System.out.println("name: " +user.getName());
        System.out.println("gender: " +user.getGender());
        System.out.println("age: " +user.getAge());
    }
    //查询操作
    @Test
    public static void queryAll() {
        //查询数据
        String SQL_QUERY = "select * from jdbctable";
        JdbcTemplate jdbcTemplete = getJDBCTemplete();
        List<User> list = jdbcTemplete.query(SQL_QUERY, new MyRowMapper());
        for (int i=0; i<list.size(); i++) {
            User user = list.get(i);
            System.out.println("name: " +user.getName());
            System.out.println("gender: " +user.getGender());
            System.out.println("age: " +user.getAge());
            System.out.println("--------------------------------");
        }
    }

    //JDBC模板，以来连接池获得数据库连接，所以必修先构建连接池
    public static JdbcTemplate getJDBCTemplete() {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/test";
        String username = "root";
        String password = "root";
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(driver);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);

        //创建JDBC模板
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        return jdbcTemplate;
    }
}