详解Java基础知识——JDBC

import java.sql.*;

public class demo02 {

   public static void main(string[] args) throws classnotfoundexception, sqlexception {

     //1注册驱动

     class .forname( "com.mysql.jdbc.driver" );

     //2建立连接

     string url = "jdbc:mysql://localhost:3306/mydb01" ;

     string usernname = "xxx" ; //登录数据库的账号

     string password = "xxxx" ; //登录数据库的密码

     connection conn = drivermanager.getconnection(url, usernname, password);

     //3获取执行sql语句的对象

     statement statement = conn.createstatement();

     //4获取数据库返回的结果

     string sql = "delete from emp where empno = " + "7499" ;

     string sqlupdate = "update emp set sal = " + 10000 + " where empno = " + "7369" ;

     string sqlinsert = "insert into emp values( 2018 ,\"boss\",\"king\", null ,\" 2018 - 8 -

               8 \ ",15000,10000,10);" ;

     //5处理数据集

     int i = statement.executeupdate(sql);

     int s = statement.executeupdate(sqlupdate);

     int ins = statement.executeupdate(sqlinsert);

     system.out.println(i + "行受到影响----删除" );

     system.out.println(s + "行受到影响----更新" );

     system.out.println(ins + "行受到影响----插入" );

     //6关闭连接

     statement.close();

     conn.close();

   }

}

import java.io.bufferedreader;

import java.io.filereader;

import java.io.ioexception;

import java.sql.*;

import java.util.properties;

public class ioreadprop {

   public static void main(string[] args) throws classnotfoundexception, sqlexception, ioexception {

     //1注册驱动

     class .forname( "com.mysql.jdbc.driver" );

     string[] para = read();

     //2建立连接

     string url = para[ 0 ];

     string usernname = para[ 1 ];

     string password = para[ 2 ];

     connection conn = drivermanager.getconnection(url,usernname,password);

     //3获取执行sql语句的对象

     statement statement = conn.createstatement();

     //4获取数据库返回的结果

     string sql = "select * from emp" ;

     resultset resultset = statement.executequery(sql);

     //5处理数据集

     try {

       while (resultset.next()){

         //.getxxx方法中的参数 1，字段名 2.字段的下标

         int empno = resultset.getint( "empno" );

         string ename = resultset.getstring( "ename" );

         string job = resultset.getstring( 3 );

         date date = resultset.getdate( 5 );

         system.out.println( "empno：" +empno+ ", ename:" +ename+ ", job:" +job+ ", date:" +date);

       }

     }

     catch (exception e){

       e.printstacktrace();

     }

     finally {

       //6关闭连接

       resultset.close();

       statement.close();

       conn.close();

     }

   }

   public static string [] read() throws ioexception {

     filereader fr = new filereader( "e:\\javalearning\\src\\jdbc\\jdbc.properties" );

     //创建 写入 缓冲区

     bufferedreader bufferedreader = new bufferedreader( fr );

     string [] str = new string[ 3 ];

     for ( int i = 0 ;i < 3 ;i++){

       str[i] = bufferedreader.readline().split( "=" )[ 1 ].replace( ";" , "" ).trim();

     }

     bufferedreader.close();

     fr.close();

     return str;

   }

}

import java.io.*;

import java.sql.*;

import java.util.iterator;

import java.util.properties;

public class propreadprop {

   public static void main(string[] args) throws classnotfoundexception, sqlexception, ioexception {

     //1注册驱动

     class .forname( "com.mysql.jdbc.driver" );

     //用户数组存放数据库信息

     string[] para = new string[ 3 ];

     //读取配置文件

     int i = 0 ;

     properties prop = new properties();

     fileinputstream fileinputstream = new fileinputstream( "e:\\javalearning\\src\\jdbc\\jdbc.properties" );

     inputstream in = new bufferedinputstream(fileinputstream);

     prop.load(in);

     iterator<string> it = prop.stringpropertynames().iterator();

     while (it.hasnext()) {

       para[i] = prop.getproperty(it.next());

       i++;

     }

     in.close();

     //2建立连接

     string url = para[ 0 ];

     string usernname = para[ 1 ];

     string password = para[ 2 ];

     connection conn = drivermanager.getconnection(url, usernname, password);

     //3获取执行sql语句的对象

     statement statement = conn.createstatement();

     //4获取数据库返回的结果

     string sql = "select * from emp" ;

     resultset resultset = statement.executequery(sql);

     //5处理数据集

     try {

       while (resultset.next()) {

         //.getxxx方法中的参数 1，字段名 2.字段的下标

         int empno = resultset.getint( "empno" );

         string ename = resultset.getstring( "ename" );

         string job = resultset.getstring( 3 );

         date date = resultset.getdate( 5 );

         system.out.println( "empno：" + empno + ", ename:" + ename + ", job:" + job + ", date:" + date);

       }

     } catch (exception e) {

       e.printstacktrace();

     } finally {

       //6关闭连接

       resultset.close();

       statement.close();

       conn.close();

     }

   }

}

package jdbc.dao;

import jdbc.entity.emp;

import jdbc.utils.jdbcutils;

import java.sql.connection;

import java.sql.resultset;

import java.sql.sqlexception;

import java.sql.statement;

public class empdao {

   /**

    * 根据员工id获取员工信息

    */

   public emp getempbyid(integer id){

     connection connection = null ;

     statement statement = null ;

     resultset rs = null ;

     emp emp = null ;

     try {

       connection = jdbcutils.getconnection();

       statement = connection.createstatement();

       rs = statement.executequery( "select * from emp where empno='" +id+ "'" );

       while (rs.next()){

         emp = new emp();

         int empno = rs.getint( "empno" );

         emp.setempno(empno);

         string ename = rs.getstring( "ename" );

         emp.setename(ename);

         string job = rs.getstring( 3 );

         emp.setjob(job);

         string hiredate = rs.getstring( 5 );

         emp.sethiredate(hiredate);

       }

     } catch (sqlexception e) {

       e.printstacktrace();

     }

     finally {

       jdbcutils.close(connection,statement,rs);

     }

     return emp;

   }

   public emp getempbyid(string id){

     connection connection = null ;

     statement statement = null ;

     resultset rs = null ;

     emp emp = null ;

     try {

       connection = jdbcutils.getconnection();

       statement = connection.createstatement();

       rs = statement.executequery( "select * from emp where empno=" +id);

       while (rs.next()){

         emp = new emp();

         int empno = rs.getint( "empno" );

         emp.setempno(empno);

         string ename = rs.getstring( "ename" );

         emp.setename(ename);

         string job = rs.getstring( 3 );

         emp.setjob(job);

         string hiredate = rs.getstring( 5 );

         emp.sethiredate(hiredate);

       }

     } catch (sqlexception e) {

       e.printstacktrace();

     }

     finally {

       jdbcutils.close(connection,statement,rs);

     }

     return emp;

   }

}

package jdbc.entity;

public class emp {

   //emp表中的相关属性

   private integer empno;

   private string ename;

   private string job;

   private string mgr;

   private string hiredate ;

   private double sal;

   private double comm;

   private integer deptno;

   public integer getempno() {

     return empno;

   }

   public void setempno(integer empno) {

     this .empno = empno;

   }

   public string getename() {

     return ename;

   }

   public void setename(string ename) {

     this .ename = ename;

   }

   public string getjob() {

     return job;

   }

   public void setjob(string job) {

     this .job = job;

   }

   public string getmgr() {

     return mgr;

   }

   public void setmgr(string mgr) {

     this .mgr = mgr;

   }

   public string gethiredate() {

     return hiredate;

   }

   public void sethiredate(string hiredate) {

     this .hiredate = hiredate;

   }

   public double getsal() {

     return sal;

   }

   public void setsal( double sal) {

     this .sal = sal;

   }

   public double getcomm() {

     return comm;

   }

   public void setcomm( double comm) {

     this 测试数据m = comm;

   }

   public integer getdeptno() {

     return deptno;

   }

   public void setdeptno(integer deptno) {

     this .deptno = deptno;

   }

   //默认输出方法

   @override

   public string tostring() {

     return "emp{" +

         "empno=" + empno +

         ", ename='" + ename + '\ '' +

         ", job='" + job + '\ '' +

         ", mgr='" + mgr + '\ '' +

         ", hiredate='" + hiredate + '\ '' +

         ", sal=" + sal +

         ", comm=" + comm +

         ", deptno=" + deptno +

         '}' ;

   }

}

package jdbc.utils;

import java.io.bufferedinputstream;

import java.io.fileinputstream;

import java.io.ioexception;

import java.io.inputstream;

import java.sql.*;

import java.util.iterator;

import java.util.properties;

public class jdbcutils {

   private static final string url ;

   private static final string username ;

   private static final string password ;

   static {

     string [] parp = null ;

     try {

       parp = propread();

     } catch (ioexception e) {

       e.printstacktrace();

     }

     url = parp[ 0 ];

     username = parp[ 1 ];

     password=parp[ 2 ];

     try {

       class .forname( "com.mysql.jdbc.driver" );

     } catch (classnotfoundexception e) {

       e.printstacktrace();

     }

   }

   /**

    * 创建连接

    */

   public static connection getconnection() throws sqlexception {

     connection conn = drivermanager.getconnection(url, username, password);

     return conn;

   }

   public static void close(connection co , statement state, resultset rs){

     if (rs != null ){

       try {

         rs.close();

       } catch (sqlexception e) {

         e.printstacktrace();

       }

     }

     if (state != null ){

       try {

         state.close();

       } catch (sqlexception e) {

         e.printstacktrace();

       }

     }

     if (co != null ){

       try {

         co.close();

       } catch (sqlexception e) {

         e.printstacktrace();

       }

     }

   }

   public static void close(connection co , statement state){

     if (state != null ){

       try {

         state.close();

       } catch (sqlexception e) {

         e.printstacktrace();

       }

     }

     if (co != null ){

       try {

         co.close();

       } catch (sqlexception e) {

         e.printstacktrace();

       }

     }

   }

   /**

    * 读取配置文件

    * @return

    * @throws ioexception

    */

   public static string [] propread() throws ioexception {

     string[] para = new string[ 3 ];

     int i = 0 ;

     properties prop = new properties();

     fileinputstream fileinputstream = new fileinputstream( "e:\\javalearning\\src\\jdbc\\jdbc.properties" );

     inputstream in = new bufferedinputstream(fileinputstream);

     prop.load(in);

     iterator<string> it = prop.stringpropertynames().iterator();

     while (it.hasnext()) {

       para[i] = prop.getproperty(it.next());

       i++;

     }

     in.close();

     return para;

   }

}

package jdbc;

import jdbc.dao.empdao;

import jdbc.entity.emp;

import java.util.scanner;

public class main {

   public static void main(string[] args) {

     empdao empdao = new empdao();

     system.out.println( "请输入id" );

     scanner scanner = new scanner( system.in );

     string value = scanner.nextline();

     emp emp = empdao.getempbyid (value);

     emp emp1 = empdao.getempbyid ( 7900 );

     system.out.println(emp);

     system.out.println(emp1);

   }

}

public emp getempbyid2(string id){

     connection connection = null ;

     preparedstatement statement = null ;

     resultset rs = null ;

     emp emp = null ;

     try {

       connection = jdbcutils.getconnection();

       string sql = "select * from emp where empno=?" ;

       statement = connection.preparestatement(sql);

       statement.setstring( 1 ,id);

       rs = statement.executequery();

       while (rs.next()){

         emp = new emp();

         int empno = rs.getint( "empno" );

         emp.setempno(empno);

         string ename = rs.getstring( "ename" );

         emp.setename(ename);

         string job = rs.getstring( 3 );

         emp.setjob(job);

         string hiredate = rs.getstring( 5 );

         emp.sethiredate(hiredate);

       }

     } catch (sqlexception e) {

       e.printstacktrace();

     }

     finally {

       jdbcutils.close(connection,statement,rs);

     }

     return emp;

   }