JavaWeb之Jdbc

JDBC的基本使用:本质就是sun公司提供的一套接口规范:数据库厂商提供的驱动实现类

• 1)导包—mysql-connector-java-5.1.37-bin.jar

链接：https://pan.baidu.com/s/1d0sInwPvTGHMoPbxjVyQHA
提取码：fjjk
复制这段内容后打开百度网盘手机App，操作更方便哦

• 2)注册驱动
• 3)获取数据库连接对象
• 4)准备sql
• 5)通过数据库连接对象获取执行对象
• 6)执行sql语句
• 7)返回结果
• 8)释放资源

public class JdbcDemo {
	
	public static void main(String[] args) throws Exception {
		//1)导入驱动jar包
		//2)注册驱动
		Class.forName("com.mysql.jdbc.Driver") ;
		
		//3)获取数据库连接对象
		//java.sql 类 DriverManager :驱动管理类(就是JDBC驱动服务)
		//public static Connection getConnection(String url, String user,String password)
		//url:统一资源定位符号:是链接的具体哪个数据库名
		//user:数据库的用户名
		//password:数据库的密码
		Connection conn = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/mydb_05", "root", "123456") ;
		
		//4)准备sql语句
		//操作mydb_05里面账户表;account
		String sql = "update account set balance = balance + 500 where id = 1" ; 
		
		//5)通过数据库连接对象Cononection获取执行对象java.sql.Statement:执行静态sql语句
		//Statement createStatement():创建执行对象并将sql准备发送数据库中
		Statement stmt = conn.createStatement() ;
		
		//6)执行sql:执行对象Statement
		//int executeUpdate(String sql)
		//执行给定 SQL 语句，该语句可能为 INSERT、UPDATE 或 DELETE 语句
		//7)返回结果:返回值:影响的行数
		int count = stmt.executeUpdate(sql) ;
		System.out.println("影响了"+count+"行");
		
		//8)释放资源
		stmt.close();
		conn.close();
		
		
	}
}

Jdbc中涉及相关的核心类以及核心接口的API

1.com.mysql.jdbc.Driver

---->class 驱动类 implements java.sql.Driver 接口 (驱动程序)

Class.forName(“com.mysql.jdbc.Driver”) ;为了保证向下兼容性(JDK6以前)

com.mysql.jdbc.Drvier----->这个类一加载,静态代码也会随之加载

static{
	 DriverManager.registerDriver(Driver driver) ---- 这里面已经注册过了
}

2.获取数据连接对象 核心类 DriverManager

–>驱动管理类----管理JDBC驱动程序服务!

 成员方法
			public static void registerDriver(Driver driver):注册驱动
			public static Connection getConnection(	
									String url,
   									String user,
                                   String password):获取数据库的连接对象
                            通过创建系统资源发送到数据库的一种url请求 
            参数1:
            	url:统一资源定位符
            	协议://域名或者ip地址:端口号/链接具体地址
            	jdbc:mysql://localhost:3306/数据库名称
             参数2:用户名:root用户
             参数3:密码

3.Connection

—java.sql.Connection 与指定的数据库的一种会话!----通过系统资源获取

成员方法
有关事务管理相关的方法
void setAutoCommit(boolean autoCommit):设置数据库的提交方式 :
参数为true:表示自动提交
参数为false:表示禁用自动提交
void rollback():事务的回滚
void commit():事务的提交

与数据库执行对象相关方法
Statement createStatement():创建Statement对象,将指定sql发送到数据库中
PreparedStatement prepareStatement(String sql):将

类似于
insert into account values(?,?,?) ; sql语句先进行预编译

4.Statement

----java.sql.Statement 执行对象:执行静态sql语句

静态的sql语句
		insert into account values(1,'张三',1000),(2,'李四',1000);
		update account set balance = balance -500 where id = 1 ;

成员方法
int executeUpdate(String sql) : 通过Statement将静态sql语句发送到数据库中进行操作
执行增删该

ResultSet executeQuery(String sql):执行查询语句select…
select * from student ;

5.PreparedStatement

java.sql.PreparedStatement:预编译对象
insert into account values(?,?,?) ;

int executeUpdate():对预编译对象中sql语句直接操作!

6.ResultSet

----java.sql.ResultSet:表示数据库结果集的数据表

成员方法
boolean next():判断下一行是否存在有效数据!

XXX getXXX(int columnIndex):根据列的索引值获取表中结果数据:索引值1开始
如果是第一列,则为1
…第二列,则为2
XXX getXXX(String columnLabel):根据列的名称获取

手动jdbc工具类的封装

jdbc.properties文件

url=jdbc:mysql://localhost:3306/mydb_05
user=root
password=123456
driverClass=com.mysql.jdbc.Driver

针对原生Jdbc的封装

public class JdbcUtils {
	
	//成员变量的位置
	public static String url = null ;
	public static String user = null ;
	public static String password = null ;
	
	//提供一个属性
	public static String driverClass = null ;
	
	
	
	//提供一个静态代码块
	//在加载JdbcUtils类的时候,静态代码中的内容优先执行
	
	static {
		
		try {
			//创建一个属性集合类对象
			Properties prop = new Properties() ;
			
			//将jdbc.properties的配置文件内容加载到prop属性集合中
			 InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties") ;
			 
			 //load(Reader r) 
			 //load(InputStream in)
			 prop.load(inputStream);
			 //System.out.println(prop);
			 
			 //通过prop的键获取值
			  url = prop.getProperty("url") ;
			  user = prop.getProperty("user") ;
			  password = prop.getProperty("password") ;
			  driverClass = prop.getProperty("driverClass") ;
			  
			 //注册驱动
			  Class.forName(driverClass) ;
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} 
	}
	
	
	//构造方法私有化
	private JdbcUtils() {}
	
	//静态功能
	//获取连接对象--封装
	public static Connection getConnection() {
		//需要通过驱动管理类获取 DriverManager
		//三个参数需要放在配置文件 xx.properites----将三个参数:jdbc.properties
		
		try {
			Connection conn  = DriverManager.getConnection(url, user, password);
			return conn ;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null ;
	}
	//关闭资源---封装
	//针对DQL语句进行操作:释放资源
	public static void close(ResultSet rs,Statement stmt,Connection conn) {
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(stmt!=null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	//针对DDL/DML:增删改 释放资源
	public static void close(Statement stmt,Connection conn) {
		
		close(null, stmt, conn);
	}
	
	
	
	//测试
	public static void main(String[] args) {
		Connection conn = JdbcUtils.getConnection() ;
		System.out.println(conn);
	}
}

举例: 使用三层架构实现USER的基本操作

注意:

在项目的src目录下创建一个文件:
jdbc.perporties

URL=jdbc:mysql://localhost:3308/school
PWD=666666
USER=root
DRIVERCLASS=com.mysql.jdbc.Driver

1,创建utils包并封装JdbcUtils工具类:

package com.utils.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
	private JdbcUtils() {
	}

	// 可以把几个字符串定义成常量：用户名，密码，URL，驱动类
	public static  String USER = null;
	public static  String PWD = null;
	public static  String URL = null;
	public static  String DRIVERCLASS = null;
	/**
	 * 注册驱动
	 */
	static {
		try {
			Properties prop = new Properties();
			InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.perporties");
			prop.load(inputStream);
			USER = prop.getProperty("USER");
			PWD = prop.getProperty("PWD");
			URL = prop.getProperty("URL");
			DRIVERCLASS = prop.getProperty("DRIVERCLASS");
			Class.forName(DRIVERCLASS);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 得到数据库的连接
	 */
	public static Connection getConnection()  {
		try {
			Connection conn = DriverManager.getConnection(URL, USER, PWD);
			return conn;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
			
	}

	/**
	 * 关闭所有的资源
	 */
	public static void close(Statement stmt, Connection conn, ResultSet rs) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	//针对DDL/DML:增删改 释放资源
	public static void close(Statement stmt,Connection conn) {
		close(stmt, conn, null);
	}

}
	 

2,创建po实体类包,并创建User实体类

package com.utils.po;

import java.sql.Date;

public class User {
	private int id;
	private String username;
	private String password;
	private String email;
	private Date birthday;
	private String address;

	public User() {
		super();
	}

	public User(int id, String username, String password, String email, Date birthday, String address) {
		super();
		this.id = id;
		this.username = username;
		this.password = password;
		this.email = email;
		this.birthday = birthday;
		this.address = address;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", password=" + password + ", email=" + email
				+ ", birthday=" + birthday + ", address=" + address + "]";
	}

}

3,创建dao接口层,并创建UserDao接口

package com.utils.Dao;
import java.util.List;
import com.utils.po.User;
public interface UserDao {
	//添加用户
	 public abstract void insertData();
	//通过用户id删除用户
	 public abstract void delete(int id);
	//通过用户id查询指定的用户
	 public abstract User findById(int id);
	//查询所有用户
	 public  abstract List<User> findAll();
	//查询总记录数
	 public abstract int counts();
}

4,创建dao接口实现包impl,并创建UserDaoImpl类,实现接口

package com.utils.Dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.utils.Dao.UserDao;
import com.utils.po.User;
import com.utils.utils.JdbcUtils;

public class UserDaoImpl implements UserDao {

	@Override
	public void insertData() {
		Connection conn= null;
        PreparedStatement prs = null;
		
	    try {
	    	conn = JdbcUtils.getConnection();
	        String sql = "insert into user(username,password,email,birthday,address) "
	        		+ "values('张三丰','123456','123456@qq.com','2020-02-20','西安')";
			prs = conn.prepareStatement(sql);
			int rs = prs.executeUpdate();
			System.out.println(rs);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(prs, conn);
		}
	}

	@Override
	public void delete(int id) {
		Connection coon = null;
		PreparedStatement prs = null;
		try {
			coon = JdbcUtils.getConnection();
			String sql = "delete from user where id=?";
			prs = coon.prepareStatement(sql);
			prs.setInt(1, id);
			int cout = prs.executeUpdate();
			System.out.println(cout);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	@Override
	public User findById(int id) {
		Connection conn =null;
		PreparedStatement prs = null;
		ResultSet rs = null;
		User user = new User();
		
	    try {
			conn =JdbcUtils.getConnection();
			String sql = "select * from user where id=?";
			prs = conn.prepareStatement(sql);		
			prs.setInt(1, id);
			rs = prs.executeQuery();
			while(rs.next()) {
				
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setEmail(rs.getString("email"));
				user.setBirthday(rs.getDate("birthday"));
				user.setAddress(rs.getString("address"));
				
			}
			
			return user;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(prs, conn, rs);
		}
	    return null;
	}

	@Override
	public List<User> findAll() {
		Connection conn =null;
		PreparedStatement prs = null;
		ResultSet rs = null;
		ArrayList<User> list =new ArrayList<User>();
		
	    try {
			conn =JdbcUtils.getConnection();
			String sql = "select * from user";
			prs = conn.prepareStatement(sql);		
			rs = prs.executeQuery();
			while(rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setEmail(rs.getString("email"));
				user.setBirthday(rs.getDate("birthday"));
				user.setAddress(rs.getString("address"));
				list.add(user);
			}
			
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(prs, conn, rs);
		}
	    return null;
	}

	@Override
	public int counts() {
		Connection conn =null;
		PreparedStatement ps = null;
		ResultSet rs = null;
			try {
				conn = JdbcUtils.getConnection();
				String sql = "select count(id) from user";
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();			
				rs.next();
				int count = rs.getInt("count(id)");
				return count;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				JdbcUtils.close(ps, conn, rs);
			}
			
		return 0;
	}

}

5,创建 conteler包,测试User

package com.utils.conteler;
import com.utils.Dao.impl.UserDaoImpl;
import com.utils.po.User;
public class UserConteler {
   public static void main(String[] args) {
	UserDaoImpl udi = new UserDaoImpl();
	//udi.insertData();
		/* udi.delete(5); */
	System.out.println(udi.findById(4));
	for(User list:udi.findAll()) {
	    System.out.println(list);
	}
	udi.counts();
}
}