jdbc综合练习

查

package com.hm.example;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.hm.pojo.Brand;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class BrandTest {
 @Test
 public void testSelectAll() throws Exception {
     Properties properties=new Properties();
     properties.load(new FileInputStream("src/main/droid.properties"));
     DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
     String sql="select*from brand";
     Connection connection = dataSource.getConnection();
     PreparedStatement preparedStatement = connection.prepareStatement(sql);
     ResultSet resultSet = preparedStatement.executeQuery();
     List<Brand>list=new ArrayList<Brand>();
     Brand brand=null;
     while (resultSet.next()){
         int id = resultSet.getInt(1);
         String brandName = resultSet.getString("brand_name");
         String companyName = resultSet.getString(3);
         int ordered = resultSet.getInt(4);
         String description = resultSet.getString(5);
          int status = resultSet.getInt(6);
          brand = new Brand(id,brandName,companyName,ordered,description,status);
          list.add(brand);
       }
      System.out.println(list);
      System.out.println(System.getProperty("user.dir"));
  }
}

增

package com.hm.example;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.hm.pojo.Brand;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class BrandTest2 {
    @Test
    public void testInsert() throws Exception {
        Properties properties=new Properties();
        properties.load(new FileInputStream("src/main/droid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        String sql="insert into brand(brand_name,company,ordered,descripition,status)values('香飘飘','香飘飘',75,'太好喝了','1')";
        Connection connection = dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        int i = preparedStatement.executeUpdate();
        if(i>0){
            System.out.println("添加成功");
        }else{
            System.out.println("添加失败");
        }



    }
}

改

package com.hm.example;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;

public class BrandTest3 {
    @Test
    public void testUpdate() throws Exception {
        Properties properties=new Properties();
        properties.load(new FileInputStream("src/main/droid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        String sql="update brand set brand_name='孙悟空'where id=4";
        Connection connection = dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        int i = preparedStatement.executeUpdate();
        System.out.println(i>0);



    }
}

删

package com.hm.example;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;

public class BrandTest4 {
    @Test
    public void testDelete() throws Exception {
        Properties properties=new Properties();
        properties.load(new FileInputStream("src/main/droid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        String sql="delete from brand where id=4";
        Connection connection = dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        int i = preparedStatement.executeUpdate();
        System.out.println(i>0);



    }
}