javaee学习日记之java基础之jdbc

MySql数据库连接

原始连接

import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test";
        String username = "root";
        String password = "123456";
        String driver = "com.mysql.jdbc.Driver";
        String name = "tom";

        try (
                Connection conn = DriverManager.getConnection(url, username, password);

            ) {
            String sql = "SELECT * FROM USERS WHERE UESRNAME='" + name + "'";

            Statement stm = conn.createStatement();

            ResultSet rs = stm.executeQuery(sql);

            if (rs.next()) {

                System.out.println("存在" + rs.getString("username"));

            } else {

                System.out.println("不存在");

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

db.properties文件的内容
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=123456
initialSize=10
maxActive=50 
maxIdle=20 
minIdle=5 
maxWait=60000

----------------------------------------------------//原始工具类（获取MySql数据库连接）
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;
    static {

        Properties properties = new Properties();//properties文件类
        try {
            properties.load(DBUtils.class.getClassLoader().getResourceAsStream("db.properties"));//加载文件
            url =  properties.getProperty("url");//获取文件中的url
            user = properties.getProperty("user");//获取文件中的user
            password = properties.getProperty("password");//获取文件中的password
            driver = properties.getProperty("driver");//获取文件中的driver
        } catch (IOException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        try {
            Class.forName(driver);//进行加载驱动

        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public static Connection getConnection() {

        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);//获取连接
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return conn;//返回连接
    }

    public static void removeConnection(Connection conn){
        if(conn != null){//判断连接是否还在继续
            try {
                conn.close();//关闭资源
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}
----------------------------------------------------//apache工具类（获取MySql数据库连接）
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.apache.commons.dbcp2.BasicDataSourceMXBean;

public class ConnectionSource {
    private static BasicDataSource bs;
    static {

        Properties properties = new Properties();//properties文件类
        try {
            properties.load(ConnectionSource.class.getClassLoader().getResourceAsStream("db.properties"));//加载文件
             bs = BasicDataSourceFactory.createDataSource(properties);//加载驱动
        } catch (IOException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


    }

    public static Connection getConnection() {

        Connection conn = null;
        try {
            conn = bs.getConnection();//获取连接
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return conn;//返回连接
    }

    public static void removeConnection(Connection conn){
        if(conn != null){//判断连接是否断开
            try {
                conn.close();//释放资源
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

数据库查询

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class EmpDao {
    public static void main(String[] args) {
        try {
            Connection connection = ConnectionSource.getConnection();//通过工具类进行数据库连接

            Statement st = connection.createStatement();//获得数据库操作对象

            String sql = "select * from emp";//sql语句

            ResultSet rs = st.executeQuery(sql);//查询结果集对象

            while(rs.next()){//判断是否有数据
                System.out.println(rs.getInt("empno") + " " + rs.getString("ename") + " " +rs.getDouble("sal") + " " + rs.getDate("hiredate"));//输出查询结果(其中一条)
            }

            ConnectionSource.removeConnection;通过工具类释放资源
        }  catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

Dao工具类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import cn.baidu.dao.ConnectionSource;

public class EmpDao {
   private static Connection con;

   public static void add(Emp emp){
       PreparedStatement ps = null;
       try {
        String sql = "insert into emp value(?,?,?,?,?,?,?,?)";
        con = ConnectionSource.getConnection();
        ps =  con.prepareStatement(sql);
        ps.setInt(1,emp.getEmpno());
        ps.setString(2,emp.getEname());
        ps.setString(3, emp.getJob());
        ps.setInt(4,emp.getMgr());
        ps.setDate(5,emp.getHiredate());
        ps.setDouble(6,emp.getSal());
        ps.setDouble(7,emp.getComm());
        ps.setInt(8, emp.getDeptno());

        int cont = ps.executeUpdate();
        if(cont>0){
            System.out.println("插入了" + cont +"条数据");
        }else{
            System.out.println("插入失败");
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally {
        try {
            ps.close();
            ConnectionSource.removeConnection(con);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
   }
   public static void del(Emp emp){
       PreparedStatement ps = null;
       try {
           String sql = "DELETE FROM emp WHERE empno = ?";
        con = ConnectionSource.getConnection();
        ps =  con.prepareStatement(sql);
        ps.setInt(1,emp.getEmpno());

        int cont = ps.executeUpdate();
        if(cont>0){
            System.out.println("删除了" + cont +"条数据");
        }else{
            System.out.println("删除失败");
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally {
        try {
            ps.close();
            ConnectionSource.removeConnection(con);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
   }
   public static void qu(Emp emp){
       PreparedStatement ps = null;
       try {
           String sql = "UPDATE emp SET sal = ? WHERE empno = ?";
        con = ConnectionSource.getConnection();
        ps =  con.prepareStatement(sql);
        ps.setDouble(1,emp.getSal());
        ps.setInt(2,emp.getEmpno());

        int cont = ps.executeUpdate();
        if(cont>0){
            System.out.println("修改" + cont +"条数据");
        }else{
            System.out.println("修改失败");
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally {
        try {
            ps.close();
            ConnectionSource.removeConnection(con);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
   }
}

批处理

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import cn.baidu.dao.ConnectionSource;

public class Batch {
    public static void main(String[] args) {
        batchAdd();
    } 
    public static void batchAdd(){
        Connection conn = ConnectionSource.getConnection();
        try {
            Statement sm = conn.createStatement();
            for(int i = 1;i<=100;i++){
                String ename = "'name" + i + "'";
                Double dou = Math.random() * 10000;
                String sql = "insert into emp(ename,sal) value(" + ename + "," + dou +")";
                sm.addBatch(sql);
            }
            sm.executeBatch();
            sm.clearBatch();

            boolean bo = conn.getAutoCommit();
            conn.setAutoCommit(false);
            conn.commit();

            conn.setAutoCommit(bo);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
           ConnectionSource.removeConnection(conn);
        }

    }
}

批查询

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import cn.baidu.dao.ConnectionSource;

public class EmpDAO {
    public static void main(String[] args) {
        findByPageMySQL();
    }

    public static void findByPageMySQL() {
     int x = 0;
     int y = 50;
     Connection conn = ConnectionSource.getConnection();
     Statement sm = null;
    try {
        sm = conn.createStatement();
        for(int i = 0;i<20000;i++){
             x = 50*i;
             String sql = "select * from persons " + " limit " + x + "," + y ;
             ResultSet rs = sm.executeQuery(sql);
             while(rs.next()){
                 System.out.print( rs.getInt("id") + ":" + rs.getString("lastname"));
             }
             System.out.println();
         }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally {
        ConnectionSource.removeConnection(conn);
    }
    }
}

批更新

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import cn.baidu.dao.ConnectionSource;

public class Trans {
    public static void main(String[] args) {
        transfer("'A'","'B'",100.00);
    }

    public static void transfer(String from, String to, double amount) {
        String sql = "update account set amount = amount - " + amount + "where id = " + from;
        String sql2 = "update account set amount = amount + " + amount + "where id = " + to;
        Connection conn = null;
        try {
            conn = ConnectionSource.getConnection();
            Statement sm = conn.createStatement();
            sm.executeUpdate(sql);
            sm.executeUpdate(sql2);

            boolean bo = conn.getAutoCommit();
            conn.setAutoCommit(false);
            conn.commit();

            conn.setAutoCommit(bo);

        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            ConnectionSource.removeConnection(conn);
        }

    }

}