JDBC基础:操作流程

JDBC基础:操作流程

1、jdbc操作流程

package com.xuedao.jdbc;


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


//添加数据

public class JdbcTest {
	
	private static final String URL = "jdbc:mysql://localhost:3306/new_test";//数据库地址
	private static final String DRIVER = "com.mysql.jdbc.Driver";//驱动类的类路径
	private static final String USER = "root";
	private static final String PASSWORD = "1814949561";
	
	private static Connection conn;
	private static PreparedStatement sta;

	public static void main(String[] args) {
		
		try {
			//1. 加载驱动类
			Class.forName(DRIVER);
			//2. 获取数据库连接
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			//3. 构建SQL语句
			String sql = "insert into student values (4,\"王彪\",'女',100)";
			//4. 获取SQL语句的执行对象
			sta = conn.prepareStatement(sql);
			//5. 执行SQL
			int i = sta.executeUpdate();
			//6. 处理结果
			if(i != -1) {
				System.out.println("操作成功");
			}
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//7. 关闭相关资源
			try {
			if(sta != null) {
				sta.close();
			}
			if(conn != null) {
				conn.close();
			}
			}catch (Exception e) {
				e.printStackTrace();
			}
		}
	

	}

}

2、一个示例----操作student表

package com.xuedao.pojo;

public class Student {

	private String username;
	private String sex;
	private int id;
	private int score;
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public int getScore() {
		return score;
	}
	public void setScore(int score) {
		this.score = score;
	}
	@Override
	public String toString() {
		return "Student [username=" + username + ", sex=" + sex + ", id=" + id + ", score=" + score + "]";
	}
	
	
}

package com.xuedao.jdbc;

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

import com.xuedao.pojo.Student;

public class JdbcUpdate {

	private static final String URL = "jdbc:mysql://localhost:3306/new_test";//数据库地址
	private static final String DRIVER = "com.mysql.jdbc.Driver";//驱动类的类路径
	private static final String USER = "root";
	private static final String PASSWORD = "1814949561";
	
	private static Connection conn;
	private static PreparedStatement sta;
	private static String sql = "";
	
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Student s = new Student();
		s.setId(4);
		s.setUsername("白素贞");
		s.setSex("女");
		s.setScore(101);		
		update(s);
		//select();
		
//封装后用此语句进行增删改查  		
//		String sql = "";
//		execute(sql, null);
	}
	/**
	 * 修改数据
	 * @param s
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void update(Student s){
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			//关闭自动提交事务
			conn.setAutoCommit(false);
			sql = "update student set username = ?, sex = ?, score = ? where id = ?";
			sta = conn.prepareStatement(sql); 
			//Statement sta = conn.createStatement();
			
			//设置参数
			sta.setString(1, s.getUsername());
			sta.setString(2, s.getSex());
			sta.setInt(3, s.getScore());
			sta.setInt(4, s.getId());
			sta.executeUpdate();
			
			//再添加一个数据
			sql = "insert into student(username,sex,score) values(?,?,?)";
			sta = conn.prepareStatement(sql);			
			sta.setString(1, s.getUsername());
			sta.setString(2, s.getSex());
			sta.setInt(3, s.getScore());
			sta.executeUpdate();
			conn.commit();
		
		} catch (Exception e) {
			//执行失败就回滚
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally {
			
				try {
					if(conn != null) {
					conn.close();
					}
					if( sta != null) {
						sta.close();
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		
	
	}
	
	
	
	/**
	 * 查询数据
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public static void select() throws ClassNotFoundException, SQLException {
		Class.forName(DRIVER);
		conn = DriverManager.getConnection(URL, USER, PASSWORD);
		
	
		
		sql = " select * from student where id = ?";
		sta = conn.prepareStatement(sql); 
		sta.setInt(1, 2);
		
		//查询所有结果
		ResultSet rs = sta.executeQuery();
		List<Student> lis = new ArrayList<>();
		//处理结果
		while (rs.next()) {
			Student s = new Student();
			s.setId(rs.getInt("id"));
			s.setUsername(rs.getString("username"));
			s.setScore(rs.getInt("score"));
			s.setSex(rs.getString("sex"));
			lis.add(s);
		}
		for (Student s : lis) {
			System.out.println(s);
		}
		if( sta != null) {
			sta.close();
		}
		if(conn != null) {
			conn.close();
		}
		if(rs != null) {
			rs.close();
		}
	}
	
	
	//封装dml
	public static boolean execute(String sql,Object...os) {
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			sta = conn.prepareStatement(sql); 
			int i = 1;
			for (Object o : os) {
				sta.setObject(i++, o);
			}
			sta.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	
	
	
	
	
	

}