Mysql & JDBC 测试

1. 建立两张表
商品类型表
create table commoditytype(
ctid int auto_increment primary key， 整数自增主键
ctname varchar(50) not null unique key 字符串最大长度50,不能为空，不能重复
);

商品信息表
create table commodityinfo(
cmmit int auto_increment primary key, 整数自增主键
ctid int not null references commoditytype(ctid), 外键关联z商品类型表主键ctid，不能为空
cmmname varchar(100) not null, 字符串最大长度100,不能为空
cmmprice decimal(10,2) not null, 小数最大长度10，精度保留2位，不能为空
salecount int not null 整数，不能为空
);
 

2. 创建JDBC

复杂方法

创建mysql.properties，将四大金刚数据放入其中，供后续调用
mysql.url = "jdbc:mysql://192.168.145.222:3306/test?useSSL=true&useUnicode=true&characterEncoding=utf-8";
mysql.user = "root";
mysql.password = "root";
mysql.driver="com.mysql.jdbc.Driver";

package org.zzz;

import java.io.FileReader;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;

public class exercise{
private static Properties prop;

public static void init(String path)throws IOException,ClassNotFoundException{
        prop=new Properties();
        prop.load(new FileReader(path));
        Class.forName(prop.getProperty("mysql.driver"));
        }

private static Connection con()throws SQLException {
        return DriverManager.getConnection(
        prop.getProperty("mysql.url"),
        prop.getProperty("mysql.username"),
        prop.getProperty("mysql.password")
        );
        }

private static PreparedStatement pst(Connection con, String sql, Object...params)throws SQLException{
        PreparedStatement pst=con.prepareStatement(sql);
        if(params.length>0){
        for(int i=0;i<params.length;i++){
        pst.setObject(i+1,params[i]);
        }
        }
        return pst;
        }

private static void close(AutoCloseable...closes){
        for(AutoCloseable close:closes){
        if(null!=close){
        try{
        close.close();
        }catch(Exception e){
        e.printStackTrace();
        } 
        }
        }
        }

public static int exeUpdate(String sql,Object...params){
        int rst=-1;
        Connection con=null;
        PreparedStatement pst=null;
        try{
        con=con();
        pst=pst(con,sql,params);
        rst=pst.executeUpdate();
        }catch(SQLException e){
        e.printStackTrace();
        }finally{
        close(pst,con);
        }
        return rst;
        }

private static Map<String, Method> parseClass(Class c){
        Map<String, Method> map=new HashMap<>();
        for(Method method:c.getMethods()){
        String name=method.getName();
        if(name.startsWith("set")){
        name=name.substring(3);
        name=name.substring(0,1).toLowerCase()+name.substring(1);
        map.put(name,method);
        }

        }
        return map;

        }

private static<T> T parseObject(Class<T> c, Map<String, Method> map, ResultSet rst)//反射创建从结果集提取数据的对象
        throws IllegalAccessException,InstantiationException,SQLException, InvocationTargetException {
        T t=c.newInstance();//调用c的对象实例
        for(Map.Entry<String, Method> e:map.entrySet()){
        e.getValue().invoke(t,rst.getObject(e.getKey())); //用getValue里获取的set方法把从rst结果集(用getKey获取字段）得到的值放进对象t里
        }
        return t;
        }

public static<T> List<T> exeQuery(Class<T> c, String sql, Object...params){
        List<T> list=new ArrayList<>();
        Connection con=null;
        PreparedStatement pst=null;
        ResultSet rst=null;
        try{
        con=con();
        pst=pst(con,sql,params);
        rst=pst.executeQuery();
        Map<String, Method> map=parseClass(c);
        while(rst.next()){
        list.add(parseObject(c,map,rst));
        }
        }catch(Exception e){
        e.printStackTrace();
        }finally{
        close(rst,pst,con);
        }
        return list;
        }
        }

简单方法

package org.zzz;


import java.applet.AppletStub;
import java.sql.*;

public class testJDBC {
    private final static String url = "jdbc:mysql://192.168.145.222:3306/test?useSSL=true&useUnicode=true&characterEncoding=utf-8";
    private final static String user = "root";
    private final static String password = "root";

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection(url, user, password);

3. 通过JDBC插入10条商品类型信息和50000条商品信息（商品类型随机选择）

 插入10行数据
PreparedStatement pst=con.prepareStatement("insert into commoditytypeDemo(ctname) values ('handset'),('pad'),('laptop'),('desktop'),('camera'),('earphone'),('watcher'),('memorybank'),('harddisk'),('udisk')");
int i = pst.executeUpdate();
System.out.println("共有"+i+"行数据收到影响");
con.close();
pst.close();

4. 通过JDBC修改商品类型信息pad为padtop

修改pad为padtop
第一种
PreparedStatement pst = con.prepareStatement("update commoditytypeDemo set ctname='padtop' where ctname='pad'");
int i = pst.executeUpdate();
System.out.println("修改成功");

第二种
String sql="update commoditytypeDemo set ctname=？where ctname=?";
int i = pst.executeUpdate(sql,"padtop","pad");
System.out.println("修改成功");

5.查看每种商品类型中单价最高和最低的商品信息

查看每种商品类型中单价最高和最低的商品信息
//select * from commodityinfo 
where cmmprice>= all(select cmmprice from commodityinfo ),
group by ctid;

//select * from commodityinfo 
where cmmprice<= all(select cmmprice from commodityinfo ),
group by ctid;

select B.ctname, A.max_price,A.min_price 
from (select ctid,max(cmmprice)max_price,min(cmmprice)min_price
from commoditytype group by ctid)A
inner join commoditytype B
on A.ctid=B.ctid;

6. 查看每种商品类型中销量前三名的平均单价

查看每种商品类型中销量前三名的平均单价
select E.ctid, avg(D.cmmprice avg_price_by_top3_salecount
form(
    select ctid,sub_string_index(group_concat(salecount), ',', 3) sales 
    from(
        select ctid, salecount 
        from commodityinfo 
        group by ctid,salescount
    )A order by ctid, salescount DESC
   )B group by ctid
)C inner join commodityinfo D
on C.ctid=D.ctid 
and find_in_set(D.salecount,C.sales)>0
inner join commoditytype E
on C.ctid=E.ctid
group by E.ctid
order by avg_price_by_top3_salecount DESC;