JDBC入门笔记（mysql）

JDBC，即Java Data Base Connectivity，是Java与数据库的连接的桥梁或者插件，用Java代码就能操作数据库的增删改查、存储过程、事务等。可以实现对mysql等数据库操作。使用jdbc的优势：如果每一个数据库java都制订一套连接方式，那么当不同的数据库更新的时候，java也需要更新自己的代码，而使用jdbc，使用同一套代码来操作，使用不同的驱动程序（驱动程序由数据库厂商提供）来连接，这使得可以连接不同的数据库。JDBC工作原理图（来自百度）

jdbc操作步骤

    private static final String DRIVER =  "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/test01";
	private static final String USER = "root";
	private static final String PASS = "ForMe=520";
	1.加载数据库驱动
    Class.forName(DRIVER);
    2.通过驱动管理类获取数据库链接
    Connection conn = DriverManager.getConnection(URL,USER,PASS);
    3.执行sql语句并获取结果
    String sql = "select * from students";
    PreparedStatement psttm = (PreparedStatement)conn.prepareStatement();//也可以用 Statement sttm = conn.createStatement();
    //注意：Statement是通用静态查询，每次查询都需要编译SQL语句，而PreparedStatenent用于参数化查询，可以用？充当占位符，且数据库系统会对SQL语句进行预编译处理，
    //预编译的SQL查询语句可以在将来的查询中重用，比Statement的查询速度更快。PreparedStatement还能防止SQL注入式攻击。
    ResultSet rs = psttm.executeQuery();
    4.关闭资源

关于URL作如下解释

下面简单实现jdbc的增、删、改、查

package com.jdbc;

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

import com.mysql.jdbc.PreparedStatement;

public class JDBCTest05 {

	private static final String DRIVER =  "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/test01";
	private static final String USER = "root";
	private static final String PASS = "ForMe=520";
	public static void main(String[] args) {
		// TODO Auto-generated method stub

		checkMessage();
		//注释代码用于验证
//		insert(new Student("fff","female",25));
//		checkMessage();
//		update(new Student("fff","",99));
//		checkMessage();
//		dele("fff");
//		checkMessage();
	}

	public static Connection getConn() {//获取连接
		Connection conn = null;
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, USER, PASS);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		return conn;
	}
	
	public static void checkMessage() {//查
		System.out.println("=================================");
		Connection conn = getConn();
		String sql = "select * from students";
		PreparedStatement psttm;
		try {
			psttm = (PreparedStatement)conn.prepareStatement(sql);
			ResultSet rs = psttm.executeQuery();
			while(rs.next()) {
				System.out.println("name:" + rs.getString("name") + "---sex:"
						+ rs.getString("sex") + "---age:" + rs.getInt("age"));
				
			}
			System.out.println("=================================");
			System.out.println();
			rs.close();
			psttm.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public static int insert(Student student) {//增
		int i = 0;
		Connection conn = getConn();
		String sql = "insert into students (name,sex,age) values (?,?,?)";
		try {
			PreparedStatement psttm = (PreparedStatement)conn.prepareStatement(sql);
			psttm.setString(1, student.getName());
			psttm.setString(2, student.getSex());
			psttm.setInt(3, student.getAge());
			i = psttm.executeUpdate();
			System.out.println(i);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	
	public static int update(Student student) {//改
		Connection conn = getConn();
		String sql = "update students set age='" + student.getAge() +"' where name='" + student.getName() +"'";
		int i = 0;
		try {
			PreparedStatement psttm = (PreparedStatement)conn.prepareStatement(sql);
			i = psttm.executeUpdate();
			System.out.println(i);
			psttm.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	
	
	public static int dele(String name) {
		int i = 0;
		Connection conn = getConn();
		String sql = "delete from students where name='" + name + "'";
		try {
			PreparedStatement psttm = (PreparedStatement)conn.prepareStatement(sql);
			i = psttm.executeUpdate();
			psttm.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
}
class Student{
	private String name;
	private String sex;
	private int age;
	public Student(String name,String sex,int age) {
		this.name = name;
		this.sex = sex;
		this.age = age;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	
}

只是查看结果为原数据库中的数据

=================================
name:bbb---sex:male---age:30
name:ccc---sex:female---age:15
name:ddd---sex:female---age:16
name:eee---sex:male---age:15
=================================

增加数据之后结果

=================================
name:bbb---sex:male---age:30
name:ccc---sex:female---age:15
name:ddd---sex:female---age:16
name:eee---sex:male---age:15
=================================

1
=================================
name:bbb---sex:male---age:30
name:ccc---sex:female---age:15
name:ddd---sex:female---age:16
name:eee---sex:male---age:15
name:fff---sex:female---age:25
=================================

更改数据之后

=================================
name:bbb---sex:male---age:30
name:ccc---sex:female---age:15
name:ddd---sex:female---age:16
name:eee---sex:male---age:15
=================================

1
=================================
name:bbb---sex:male---age:30
name:ccc---sex:female---age:15
name:ddd---sex:female---age:16
name:eee---sex:male---age:15
name:fff---sex:female---age:25
=================================

删除数据

=================================
name:bbb---sex:male---age:30
name:ccc---sex:female---age:15
name:ddd---sex:female---age:16
name:eee---sex:male---age:15
=================================

1
=================================
name:bbb---sex:male---age:30
name:ccc---sex:female---age:15
name:ddd---sex:female---age:16
name:eee---sex:male---age:15
=================================

所得结果与用cmd命令打开执行相应的命令结果一致。