java课题–day4基础回顾

数据库相关

package com.oracle.utils;

import java.sql.*;

/**
 * @author Shangkejie
 * @Class BigData182
 * @date 2019/12/12 - 16:49
 */
public class DBUtils {
    private static final String DRIVER="com.mysql.jdbc.Driver";
    private static final String URL="jdbc:mysql://localhost:3306/company";
    private static final String USER="root";
    private static final String PASSWORD="skj0507";
    static {
        try {
            Class.forName(DRIVER);//注册一次驱动
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL,USER,PASSWORD);
    }
    public static void closeResultSet(ResultSet rs){
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void closePreParedStaStatement(PreparedStatement ps){
        if (ps!=null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void closeConnection (Connection conn){
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(Connection conn,PreparedStatement ps,ResultSet rs){
        closeResultSet(rs);
        closePreParedStaStatement(ps);
        closeConnection(conn);
    }
    public static void close(Connection conn,PreparedStatement ps){
        closePreParedStaStatement(ps);
        closeConnection(conn);
    }

}

数据库SQl

package com.zut.bigdata.skj;

/**
 * @author Shangkejie
 * @Class BigData182
 * @date 2019/12/12 - 10:57
 */
public class Employee {
    private int empno;
    private String ensme;
    private String job;
    private double sal;
    private  double Comm;

    public Employee() {
    }


    public Employee(int empno, String ensme, String job, double sal, double comm) {
        this.empno = empno;
        this.ensme = ensme;
        this.job = job;
        this.sal = sal;
        Comm = comm;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public int getEmpno() {
        return empno;
    }

    public void setEmpno(int empno) {
        this.empno = empno;
    }

    public String getEnsme() {
        return ensme;
    }

    public void setEnsme(String ensme) {
        this.ensme = ensme;
    }

    public double getSal() {
        return sal;
    }

    public void setSal(double sal) {
        this.sal = sal;
    }

    public double getComm() {
        return Comm;
    }

    public void setComm(double comm) {
        Comm = comm;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "empno=" + empno +
                ", ensme='" + ensme + '\'' +
                ", job='" + job + '\'' +
                ", sal=" + sal +
                ", Comm=" + Comm +
                '}';
    }
}

//junit
package com.zut.bigdata.skj;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;
import java.util.Scanner;

/**
 * @author Shangkejie
 * @Class BigData182
 * @date 2019/12/12 - 10:49
 */
public class TestCRUD {
    Connection connection;
    Statement statement;
    ResultSet resultSet;

    @Before
    public void init(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            try {
                connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/company","root","skj0507");
                statement=connection.createStatement();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    @Test
    public void testAdd(){
        System.out.println("ok");
        Scanner input=new Scanner(System.in);
        System.out.println("enmpo");
        int empno=input.nextInt();
        System.out.println("ename");
        String ename=input.nextLine();
        System.out.println("job");
        String job=input.nextLine();
        System.out.println("sal");
        double sal=input.nextDouble();
        System.out.println("Comm");
        double Comm=input.nextDouble();
        System.out.println("good");
        String sql="insert into emp values("+empno+",'"+ename+"',"+"'"+job+"',"+sal+","+Comm+")";
        try {
            int res=statement.executeUpdate(sql);
            if (res==1){
                System.out.println("一条数据已经添加!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @After
    public void close(){
        try {
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}


//sqlTest
package com.zut.bigdata.skj;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

/**
 * @author Shangkejie
 * @Class BigData182
 * @date 2019/12/12 - 10:07
 * JDBC 是面向接口编程 提高代码的重用性  ,解耦
 */
public class TestDemo {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");//只要注册一次
        /*//jdbc:mysql:  协议名称  //localhost:3306  数据库地址,端口  //company   数据库名称*/
        Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/company","root","skj0507");
        if (con!=null){
            System.out.println("连接成功!");
        }
        //句柄对象
        Statement statement=con.createStatement();
        String sql="select * from emp where emp.empno=1015";
        //发送sql语句
        ResultSet resultSet=statement.executeQuery(sql);
        Scanner input=new Scanner(System.in);
        String in=input.nextLine();
        System.out.println("编号"+"\t"+"姓名"+"\t"+"岗位"+"\t"+"薪资"+"\t"+"奖金");
        if (resultSet.next()){
            int empno=resultSet.getInt("empno");
            String ename=resultSet.getString("ename");
            String job=resultSet.getString("job");
            double sal=resultSet.getDouble("sal");
            double Comm=resultSet.getDouble("COMM");
            System.out.println(empno+"\t"+ename+"\t"+job+"\t"+sal+"\t"+Comm);
        }
        resultSet.close();
        statement.close();
        con.close();
        System.out.println("资源关闭成功!");
    }
}

C3P0

//配置文件
<c3p0-config>
  <!-- 使用默认的配置读取连接池对象 -->
  <default-config>
  	<!--  连接参数 -->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/company</property>
    <property name="user">root</property>
    <property name="password">skj0507</property>
    
    <!-- 连接池参数 -->
    <property name="initialPoolSize">5</property>
    <property name="maxPoolSize">10</property>
    <property name="checkoutTimeout">3000</property>
  </default-config>

  <named-config name="otherc3p0"> 
    <!--  连接参数 -->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/company</property>
    <property name="user">root</property>
    <property name="password">skj0507</property>
    
    <!-- 连接池参数 -->
    <property name="initialPoolSize">5</property>
    <property name="maxPoolSize">8</property>
    <property name="checkoutTimeout">1000</property>
  </named-config>
</c3p0-config>
//代码
package com;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.logging.Logger;

/**
 * @author Shangkejie
 * @Class BigData182
 * @date 2019/12/12 - 17:29
 */
public class c3p0 {
    public static void main(String[] args) {
        DataSource ds=new ComboPooledDataSource();
        try {
            Connection conn=ds.getConnection();
            if (conn!=null){
                System.out.println("链接成功!");
                System.out.println(conn);
            }else{
                System.out.println("链接失败!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

//测试2
package com;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * @author Shangkejie
 * @Class BigData182
 * @date 2019/12/12 - 17:38
 */
public class TestC3p0 {
    public static void main(String[] args) {
        DataSource ds=new ComboPooledDataSource("otherc3p0");
        for (int i = 0; i < 9; i++) {
            try {
                Connection conn=ds.getConnection();
                System.out.println(conn);
                if (i==5){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

druid

//配置文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/company
username=root
password=skj0507
initialSize=5
maxActive=10
maxWait=3000

//测试

import com.alibaba.druid.pool.DruidDataSourceFactory;

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

/**
 * @author Shangkejie
 * @Class BigData182
 * @date 2019/12/12 - 17:47
 */
public class DruidTest {
    public static void main(String[] args) throws Exception {
        Properties p = new Properties();
        p.load(new FileInputStream("src/druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(p);
        Connection connection=ds.getConnection();
        System.out.println(connection);
    }

}