jdbc-摩杜云开发者社区

1 概念

jdbc:java database connection 通过java连接数据库
sun公司为java连接所有数据库提供的一组接口;
jdbc驱动：各个数据库厂家为自己的数据库根据jdbc规范 提供的实现类

2 关联的接口

Connection: 数据库的连接
Statement: sql语句发送器
PreparedStatement: 预编译对象 Statement的子接口
Resultset:结果集
DriverManager:驱动管理器 获取连接

3 jdbc案例1

3.1 创建javaweb项目

jdbc_java

jdbc_sql_02

3.2 jdbc铁打七步

public class Demo01 {
    /*
     * 1 导入驱动： 
     *    把mysql-connector-java-5.1.15-bin.jar复制到WebContent/lib下
     *    选中jar 右键 bulid path
     * 2 准备连接数据库的四大参数
     *    驱动类名、数据库url、用户名、密码
     * 3 注册驱动：加载驱动类
     * 4 通过DriverManager获取连接 
     * 5 通过connection的createStatement方法获取sql语句发送器对象
     * 6 通过statement的executeXxx方法发送sql语句 (如果有结果集并继续结果集）
     * 7 关闭连接释放资源
     * 
     * */
	public static void main(String[] args)throws Exception {
		//2 准备四大参数
		String driverName="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/db_1";
		String user="root";
		String pwd="root";
	     //3注册驱动：加载驱动类
		Class.forName(driverName);
		//4 通过DriverManager获取连接  
		Connection con=DriverManager.getConnection(url, user, pwd);
		//5 通过con连接获取sql语句发送器对象Statement 
		Statement sta=con.createStatement();
		//6 发送并执行sql语句
		int hang=sta.executeUpdate("update student set sname='武大郎' where sid=1");
		System.out.println("修改"+hang+"行成功！");
		hang=sta.executeUpdate("insert into student(sname,sex,score) values('呵呵','妖',11)");
		System.out.println("添加"+hang+"行成功！");
		hang=sta.executeUpdate("delete from student where sid=3");
		System.out.println("删除"+hang+"行成功！");
		
		ResultSet set=sta.executeQuery("select * from student");
		while(set.next()){//判断结果集是否有行可以继续遍历
			 //获取行信息
			 int id=set.getInt("sid");
            id=set.getInt(1);
			 String name=set.getString("sname");
			 String sex=set.getString("sex");
			 float score=set.getFloat("score");
			 int age=set.getInt("sage");
			 int tid=set.getInt("stid");
			 String sclass=set.getString("sclass");
			 System.out.println(id+":"+name+":"+sex+":"+score+":"+age+":"+tid+":"+sclass);
			 Student stu=new Student(id, name, sex, score, age, tid, sclass);
			 System.out.println(stu);
		}
		//7 关闭连接 释放资源：关闭的原则：先开的后关 后开的先关
		set.close();
		sta.close();
		con.close();
	}
}

3.3 crud拆分

package com.zhiyou100_01.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Demo02 {
	/*
	 * 1 导入jar包：：：每个项目只用做一次
	 * 2 准备四大参数
	 * 3 注册驱动：加载driver类
	 * 4 获取连接
	 * 5 通过连接获取statement
	 * 6 通过statement的executexxx方法来执行sql语句：解析结果集
	 * 7 关闭连接释放资源：
	 * */
	 private static  String url,user, pwd,driver;
	 static{
		 url="jdbc:mysql://localhost:3306/db_1";
		 user="root";
		 pwd="root";
		 driver="com.mysql.jdbc.Driver";
	 }
	 static{
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(e);
		} 
	 }
	 //获取连接
	 private static Connection getCon(){
		 try {
			return DriverManager.getConnection(url, user, pwd);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	 }
	 //关闭连接
	 private static void close(Connection con,Statement sta,ResultSet set){
		 try {
			   if(set!=null){
					set.close();
				}
				if(sta!=null){
					sta.close();
				}
				if(con!=null){
					con.close();
				}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	 }
	 //Integer sid, String sname, String sex, Float score, Integer sage, Integer stid, String sclass
	 public static int updateOne(Student s)throws Exception{
		 //获取连接
		 Connection con=getCon();
		 //获取statement
		 Statement sta=con.createStatement();
		 //发送sql
		 String sql="update student set sname='"+s.getSname()+"',sex='"+s.getSex()+"',score="+s.getScore()+",sage="+s.getSage()+",stid="+s.getStid()+",sclass='"+s.getSclass()+"' where sid="+s.getSid();
		 System.out.println(sql);
		 int hang=sta.executeUpdate(sql);
		 //关闭连接
		 close(con, sta, null);
		 return hang;
	 }
	 public static int deleteOne(int sid)throws Exception{
		 //获取连接
		 Connection con=getCon();
		 //获取statement
		 Statement sta=con.createStatement();
		 //发送sql
		 String sql="delete from student where sid="+sid;
		 System.out.println(sql);
		 int hang=sta.executeUpdate(sql);
		 //关闭连接
		 close(con, sta, null);
		 return hang;
	 }
	 public static int insertOne(Student s)throws Exception{
		 //获取连接
		 Connection con=getCon();
		 //获取statement
		 Statement sta=con.createStatement();
		 //发送sql
		 String sql="insert into student(sname,sage,sex,score,stid,sclass) value('"+s.getSname()+"',"+s.getSage()+",'"+s.getSex()+"',"+s.getScore()+","+s.getStid()+",'"+s.getSclass()+"')";
		 System.out.println(sql);
		 int hang=sta.executeUpdate(sql);
		 //关闭连接
		 close(con, sta, null);
		 return hang;
	 }
	 public static Student getOne(int sid)throws Exception{
		 //获取连接
		 Connection con=getCon();
		 //获取statement
		 Statement sta=con.createStatement();
		 //发送sql
		 String sql="select * from student where sid="+sid;
		 System.out.println(sql);
		 ResultSet set=sta.executeQuery(sql);
		 Student stu=null;
		 while(set.next()){
			 stu=new Student();
			 stu.setSid(sid);
			 stu.setSname(set.getString("sname"));
			 stu.setSex(set.getString("sex"));
			 stu.setSclass(set.getString("sclass"));
			 stu.setSage(set.getInt("sage"));
			 stu.setScore(set.getFloat("score"));
			 stu.setStid(set.getInt("stid"));
		 }
		 //关闭连接
		 close(con, sta, set);
		 return stu;
	 }
	 public static List<Student> getAll()throws Exception{
		 //获取连接
		 Connection con=getCon();
		 //获取statement
		 Statement sta=con.createStatement();
		 //发送sql
		 String sql="select * from student";
		 System.out.println(sql);
		 ResultSet set=sta.executeQuery(sql);
		 List<Student> list=new ArrayList<Student>();
		 while(set.next()){
			 Student stu=new Student();
			 stu=new Student();
			 stu.setSid(set.getInt("sid"));
			 stu.setSname(set.getString("sname"));
			 stu.setSex(set.getString("sex"));
			 stu.setSclass(set.getString("sclass"));
			 stu.setSage(set.getInt("sage"));
			 stu.setScore(set.getFloat("score"));
			 stu.setStid(set.getInt("stid"));
			 list.add(stu);
		 }
		 //关闭连接
		 close(con, sta, set);
		 return list;
	 }
	 public static void main(String[] args)throws Exception {
		   //Integer sid, String sname, String sex, Float score, Integer sage, Integer stid, String sclass
//		   System.out.println("添加："+insertOne(new Student(null, "呵呵呵", "妖", 11f, 12, 1, "cla_000")));
//		   System.out.println("修改："+updateOne(new Student(12, "嘿嘿嘿", "妖", 12f, 13, 2, "cla_002")));
//		   System.out.println("删除："+deleteOne(13));
		   System.out.println("获取一个："+getOne(1));
		   System.out.println("获取所有："+getAll());
	 }
}

3.4 注意事项

注意：日期类型数据的赋值和解析
ALTER TABLE student ADD sbirth DATE;
ALTER TABLE student ADD scome DATETIME;
UPDATE student SET sbirth=NOW(),scome=NOW();

//Integer sid, String sname, String sex, Float score, Integer sage, Integer stid, String sclass
public static int insertOne(Student s)throws Exception{
    //获取连接
    Connection con=getCon();
    //获取statement
    Statement sta=con.createStatement();
    //发送sql
    //需要把日期转化为对应格式的字符串
    String sql="insert into student(sname,sage,sex,score,stid,sclass,sbirth,scome) "
        + "value('"+s.getSname()+"',"+s.getSage()+",'"+s.getSex()+"',"+s.getScore()+","+s.getStid()+",'"+s.getSclass()+"',"
        + "'"+date2Str(s.getSbirth())+"','"+date2Str(s.getScome())+"')";
    System.out.println(sql);
    int hang=sta.executeUpdate(sql);
    //关闭连接
    close(con, sta, null);
    return hang;
}
//把日期转化为制定格式的字符串：xxx/xx/xx xx:xx:xx
private static String date2Str(Date date){
    return new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(date);
}

public static Student getOne(int sid)throws Exception{
    //获取连接
    Connection con=getCon();
    //获取statement
    Statement sta=con.createStatement();
    //发送sql
    String sql="select * from student where sid="+sid;
    System.out.println(sql);
    ResultSet set=sta.executeQuery(sql);
    Student stu=null;
    while(set.next()){
        stu=new Student();
        stu.setSid(sid);
        stu.setSname(set.getString("sname"));
        stu.setSex(set.getString("sex"));
        stu.setSclass(set.getString("sclass"));
        stu.setSage(set.getInt("sage"));
        stu.setScore(set.getFloat("score"));
        stu.setStid(set.getInt("stid"));
        stu.setSbirth(set.getDate("sbirth"));
        //ResultSet的getDate()方法：返回值是java.sql.Date  只用年月日
        //如果需要获取年月日-时分秒  通过ResultSet的getTimestamp()方法来获取
        stu.setScome(set.getTimestamp("scome"));
    }
    //关闭连接
    close(con, sta, set);
    return stu;
}

4 PreparedStatement

4.1 使用

package com.zhiyou100_01.jdbc;

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 java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Demo04PreparedStatement {
	/*PreparedStatement:预编译对象
	 *Statement的子接口：sql语句发送器
	 *
	 *使用的不同之处：
	 *1 创建PreparedStatement时需要关联sql模板
	 *2 PreparedStatement调用execute方法前 需要给sql模板中的占位符赋值
	 *3 Statement执行execute方法时 需要传递拼接的sql语句  
	 * 
	 *编程过程中 选择PreparedStatement：
	 * */
	
	public static void main(String[] args) throws Exception{
		//statementCrud();
		preparedStatementCrud();
	}
	public static void statementCrud()throws Exception{
		//获取连接
		Connection con=JdbcUtil.getCon();
		//获取statement对象
		Statement sta=con.createStatement();
		//发送sql语句
		int hang;
		Teacher t;
		t=new Teacher("韩老师", "男", "111", 11111f);
		hang=sta.executeUpdate("insert into teacher(tname,tsex,tpwd,tsalary) "
				+ "values('"+t.getTname()+"','"+t.getTsex()+"','"+t.getTpwd()+"',"+t.getTsalary()+")");
		System.out.println("添加："+hang);
		t=new Teacher(1,"张三老师", "女", "111", 22222f);
		hang=sta.executeUpdate("update teacher set tname='"+t.getTname()+"',tpwd='"+t.getTpwd()+
				"',tsalary="+t.getTsalary()+",tsex='"+t.getTsex()+"' where tid="+t.getTid());
		System.out.println("修改："+hang);
		hang=sta.executeUpdate("delete from teacher  where tid="+9);
		System.out.println("删除："+hang);
		//获取所有
		List<Teacher> list=new ArrayList<Teacher>();
		ResultSet set=sta.executeQuery("select * from teacher");
		while(set.next()){
			list.add(new Teacher(set.getInt("tid"), set.getString("tname"),  set.getString("tsex"),set.getString("tpwd"), set.getFloat("tsalary")));
		}
		System.out.println("获取所有："+list);
		JdbcUtil.close(con, sta, set);
		
	}
	public static void preparedStatementCrud()throws Exception{
		//获取连接
		Connection con=JdbcUtil.getCon();
		//获取prparedstatement预编译对象 并关联sql模板：sql语句中 变量用占位符？表示
		PreparedStatement pre=null;
		int hang;
		Teacher t;
		//添加
		t=new Teacher("韩老师2", "女", "1112", 111112f);
		pre=con.prepareStatement("insert into teacher(tname,tsex,tpwd,tsalary) values(?,?,?,?)");
		//给占位符赋值
		pre.setString(1, t.getTname());//给一个字符串类型的占位符赋值：：第1个占位符 赋值t.getTname()
		pre.setString(2, t.getTsex());
		pre.setString(3, t.getTpwd());
		pre.setFloat(4, t.getTsalary());
		//执行sql语句
		hang=pre.executeUpdate();
		System.out.println("添加："+hang);
		
		//修改
		//获取prparedstatement预编译对象 并关联sql模板：sql语句中 变量用占位符？表示
		pre=con.prepareStatement("update teacher set tname=?,tsex=?,tpwd=?,tsalary=? where tid=?");
		//给占位符赋值
		t=new Teacher(2,"韩老师2", "女", "2222", 2222f);
		pre.setString(1, t.getTname());
		pre.setString(2, t.getTsex());
		pre.setString(3, t.getTpwd());
		pre.setFloat(4, t.getTsalary());
		pre.setInt(5, t.getTid());
		//执行sql语句
		hang=pre.executeUpdate();
		System.out.println("修改："+hang);
		
		//删除
		pre=con.prepareStatement("delete from teacher where tid=?");
		//给占位符赋值
		pre.setInt(1, 10);
		//执行sql语句
		hang=pre.executeUpdate();
		System.out.println("删除："+hang);
		
		//查询一个
		pre=con.prepareStatement("select * from teacher where tid=?");
		//给占位符赋值
		pre.setInt(1, 2);
		//执行sql语句
		ResultSet set=pre.executeQuery();
		if(set.next()){
			t=new Teacher(set.getInt("tid"), set.getString("tname"), set.getString("tsex"), set.getString("tpwd"), set.getFloat("tsalary"));
		}
		System.out.println("获取一个："+t);
		
		//查询所有
		List<Teacher> list=new ArrayList<Teacher>();
		pre=con.prepareStatement("select * from teacher");
		set=pre.executeQuery();
		while(set.next()){
			list.add(new Teacher(set.getInt("tid"), set.getString("tname"),  set.getString("tsex"),set.getString("tpwd"), set.getFloat("tsalary")));
		}
		System.out.println("获取所有："+list);
		JdbcUtil.close(con, pre, set);
		
	}
}

4.2 优点

package com.zhiyou100_01.jdbc;

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 java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Demo05PreparedStatement {
	/*PreparedStatement:预编译对象
	 *Statement的子接口：sql语句发送器
	 *
	 *使用的不同之处：
	 *1 创建PreparedStatement时需要关联sql模板
	 *2 PreparedStatement调用execute方法前 需要给sql模板中的占位符赋值
	 *3 Statement执行execute方法时 需要传递拼接的sql语句  
	 * 
	 *编程过程中 选择PreparedStatement：
	 *PreparedStatement优点1： 进行批量处理时：PreparedStatement只对sql模板进行一次语法检查 一次sql解析 n次执行 效率高
	 *                                      Statement需要对sql语句进行n次语法检查 n次sql解析 n次执行 效率低
	 *
	 *PreparedStatement优点2：   PreparedStatement能有效避免sql注入：
	 *    PreparedStatement把sql片段和数据分开发给数据库 数据库对数据中的特殊字符不会解析
	 *    Statement把sql片段和数据通过字符串拼接后整体发给数据库 数据库会解析数据中伪装的sql片段
	 *    
	 *PreparedStatement优点3：PreparedStatement不需要字符串拼接 sql出错的概率低
	 *                                    
	 * */
	
	public static void main(String[] args) throws Exception{
//		List<Teacher> list=new ArrayList<Teacher>();
//		for (int i = 0; i < 5; i++) {
//			list.add(new Teacher("新来的"+i, "圣", "321"+i, i*10000f));
//		}
//		//preparedStatement01(list);
//		statement01(list);
		
		preparedStatement02("王三","123456");
		statement02("王三","123456");
		
		preparedStatement02("王三三","123456");
		statement02("王三三","123456");
		
		preparedStatement02("王三三 ' or '1'='1","123456");
		statement02("王三三 ' or '1'='1","123456");
		
	}
	public static void preparedStatement02(String tname,String tpwd)throws Exception{
		//获取连接
		Connection con=JdbcUtil.getCon();
		PreparedStatement pre=con.prepareStatement("select * from teacher where tname=? and tpwd=?");		
		Teacher t=null;
		pre.setString(1, tname);
		pre.setString(2, tpwd);
		ResultSet set=pre.executeQuery();
		if(set.next()){
			t=new  Teacher(set.getInt("tid"), set.getString("tname"),  set.getString("tsex"),set.getString("tpwd"), set.getFloat("tsalary"));
		}
		if(t==null){
			System.out.println("preparedStatement02登录失败！");
		}else{
			System.out.println("preparedStatement02登录成功！"+t);
		}
		JdbcUtil.close(con, pre, set);
	}
	public static void statement02(String tname,String tpwd)throws Exception{
		//获取连接
		Connection con=JdbcUtil.getCon();
		Statement sta=con.createStatement();
		Teacher t=null;
		String sql="select * from teacher where tname='"+tname+"' and tpwd='"+tpwd+"'";
		System.out.println(sql);
		ResultSet set=sta.executeQuery("select * from teacher where tname='"+tname+"' and tpwd='"+tpwd+"'");
		if(set.next()){
			t=new  Teacher(set.getInt("tid"), set.getString("tname"),  set.getString("tsex"),set.getString("tpwd"), set.getFloat("tsalary"));
		}
		if(t==null){
			System.out.println("statement02登录失败！");
		}else{
			System.out.println("statement02登录成功！"+t);
		}
		JdbcUtil.close(con, sta, set);
	}
	
	public static void preparedStatement01(List<Teacher> list)throws Exception{
		//获取连接
		Connection con=JdbcUtil.getCon();
		PreparedStatement pre=con.prepareStatement("insert into teacher(tname,tsex,tpwd,tsalary) values(?,?,?,?)");		
		for (Teacher t : list) {
			//给占位符赋值
			pre.setString(1, t.getTname());
			pre.setString(2, t.getTsex());
			pre.setString(3, t.getTpwd());
			pre.setFloat(4, t.getTsalary());
			int hang=pre.executeUpdate();
			System.out.println("添加行："+hang+"，成功！");
		}
		JdbcUtil.close(con, pre, null);
	}
	public static void statement01(List<Teacher> list)throws Exception{
		//获取连接
		Connection con=JdbcUtil.getCon();
		Statement sta=con.createStatement();
		
		for (Teacher t : list) {
			//拼接sql语句
			int hang=sta.executeUpdate("insert into teacher(tname,tsex,tpwd,tsalary)  values('"+t.getTname()+"','"+t.getTsex()+"','"+t.getTpwd()+"',"+t.getTsalary()+")");
			System.out.println("添加行："+hang+"，成功！");
		}
		JdbcUtil.close(con, sta, null);
	}
}