JDBC基础
//1.注册安装驱动
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/myemployees";
String user="root";
String passworld="123456";
//2.连接数据库
Connection con = DriverManager.getConnection(url,user,passworld);
if (con != null){
System.out.println("数据库登录成功...");
}else {
System.out.println("数据库登录失败...");
}
String sql="delete from employees where employee_id=206;";
String sql1="SELECT * FROM `jobs`; ";
//3.获取发送SQL的对象
Statement statement=con.createStatement();
//DML语句返回int类型
int result=statement.executeUpdate(sql);
if (result ==1 ){
System.out.println("执行成功...");
}else {
System.out.println("执行失败...");
}
//DQL语句返回结果数据集
ResultSet rs= statement.executeQuery(sql1);
while (rs.next()){
String job_id=rs.getString(1);
String job_title=rs.getString(2);
String min_salary=rs.getString(3);
String max_salary=rs.getString(4);
System.out.println(job_id+"\t"+job_title+"\t"+min_salary+"\t"+max_salary);
}
//释放资源。
rs.close();
statement.close();
con.close();#常见错误
java.lang.ClassNotFoundException:找不到类（类名书写错误、没有导入jar包）
java.sql.SQLException：与sql语句相关的错误（约束错误、表名列名书写错误）：建议：在客户端工具先测试sql语句再粘贴在代码中。
com.mysql.jdbc.exceptions.jdbc.MYSQLSyntaxException:Unknown column原因：列值String类型没有加单引号 ' ' ；
Duplicate entry '1' for key 'PRIMARY'原因，主键值已存在或混乱，更改主键值或清空表
com.mysql.jdbc.exceptions.jdbc4.MYSQLSyntaxErrorException:Unknown column 'password' in 原因可能输入的值的类型不对，确定是否插入的元素时对应的值的类型正确
#SQL注入问题
用户输入的数据中有sql关键字或语法并且参与了sql语句的编译，导致sql语句编译后的条件含义为true ，一直得到正确的结果
#PreparedStatement的应用
作用：预编译sql语句，效率高。安全，避免sql注入。可动态的填充数据，执行多个同构的sql语句。
### 参数标记
PreparedStatement pstmt = con.prepareStatement("select * from user where username =? and password =? ");JDBC中的所有参数都由？符号站位，被称为参数标记，在执行sql语句前，必须为每个参数提供值。
###动态参数绑定
pstmt=setXxx(小标，值)
PreparedStatement pstmt = con.prepareStatement("select * from user where username =? and password =? ");
pstmt.setString(1,username);
pstmt.setString(2,password);#封装工具类
##重用性方案
封装获取连接、释放资源两个方法。
提供public static Connection getConnection(){}方法。
提供public static void closeAll(Connection conn,Statement sm,ResultSet rs)(){}方法
import java.sql.*;
public class DBUtils {
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection connection= null;
try {
connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/myemployees","root","123456");
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null){
statement.close();
}
if (connection != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
##跨平台方案
定义public static final Properties prop = new Properties ( );读取配置文件的Map;定义
static{
首次使用工具类时，加载驱动
private static final Properties PROPERTIES=new Properties();
InputStream is =JDBCUtil.class.getResourceAsStream("路径");
PROPERTIES.load(is);
Class.forName(PROPERTIES.getProperty("driver"));
}新建db.properties文件 内容如下
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myemployees
username=root
password=123456
在DBUtils类中添加语句
private static final Properties PROPERTIES=new Properties();
static {
InputStream is=DBUtils.class.getResourceAsStream("/db.properties");
try {
PROPERTIES.load(is);
Class.forName(PROPERTIES.getProperty("driver"));
} catch (ClassNotFoundException | IOException e) {
e.printStackTrace();
}
}#ORM
从数据库查询到结果集ResultSet在进行遍历时，逐行遍历，取出的都是零散的数据。在实际应用开发中，我们需要将零散的数据进行封装整理。
##实体类entity：零散数据的载体
一行数据中，多个零散的数据进行整理
通过entity的规则对表中的数据进行对象的封装
表名=类名；列名=属性名；提供各个属性的get、set方法
提供无参构造方法（视情况添加有参构造）
public class Jobs {
private String job_id;
private String job_title;
private int min_salary;
private int max_salary;
public Jobs() {
}
public Jobs(String job_id, String job_title, int min_salary, int max_salary) {
this.job_id = job_id;
this.job_title = job_title;
this.min_salary = min_salary;
this.max_salary = max_salary;
}
@Override
public String toString() {
return "jobs{" +
"job_id='" + job_id + '\'' +
", job_title='" + job_title + '\'' +
", min_salary=" + min_salary +
", max_salary=" + max_salary +
'}';
}
public String getJob_id() {
return job_id;
}
public void setJob_id(String job_id) {
this.job_id = job_id;
}
public String getJob_title() {
return job_title;
}
public void setJob_title(String job_title) {
this.job_title = job_title;
}
public int getMin_salary() {
return min_salary;
}
public void setMin_salary(int min_salary) {
this.min_salary = min_salary;
}
public int getMax_salary() {
return max_salary;
}
public void setMax_salary(int max_salary) {
this.max_salary = max_salary;
}
} while (rs.next()){
String job_id=rs.getString(1);
String job_title=rs.getString(2);
int min_salary=rs.getInt(3);
int max_salary=rs.getInt(4);
Jobs jobs=new Jobs();
jobs.setJob_id(job_id);
jobs.setJob_title(job_title);
jobs.setMin_salary(min_salary);
jobs.setMax_salary(max_salary);
System.out.println(jobs);
}#DAO数据访问对象（Data Access Object）
DAO实现了业务逻辑与数据库访问相分离
对同一张表的所有操作封装在XxxDaolmpl对象中。
根据增删改查的不同功能实现具体的方法（insert、update、delele、select、selectAll）
public class JobsDaoImpl {
public int insert(Jobs jobs){
String sql="insert into jobs(job_id,job_title,min_salary,max_salary)values(?,?,?,?);";
Connection connection=null;
PreparedStatement preparedStatement= null;
try {
connection=DBUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, jobs.getJob_id());
preparedStatement.setString(2, jobs.getJob_title());
preparedStatement.setInt(3,jobs.getMin_salary());
preparedStatement.setInt(4,jobs.getMax_salary());
int result=preparedStatement.executeUpdate();
return result;
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(connection,preparedStatement,null);
}
return 0;
}
public int delete(String job_id){
String sql="delete from jobs where job_id=?;";
Connection connection = null;
PreparedStatement preparedStatement= null;
try {
connection = DBUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,job_id);
int result= preparedStatement.executeUpdate();
return result;
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(connection,preparedStatement,null);
}
return 0;
}
public int update(Jobs jobs){
String sql="update jobs set job_title=?,min_salary=?,max_salary=? where job_id=?";
Connection connection=null;
PreparedStatement preparedStatement= null;
try {
connection=DBUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,jobs.getJob_title());
preparedStatement.setInt(2,jobs.getMin_salary());
preparedStatement.setInt(3,jobs.getMax_salary());
preparedStatement.setString(4,jobs.getJob_id());
int result= preparedStatement.executeUpdate();
return result;
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(connection,preparedStatement,null);
}
return 0;
}
public Jobs select(String job_id){
String sql="select * from jobs where job_id=?";
Connection connection=null;
PreparedStatement preparedStatement= null;
ResultSet resultSet=null;
Jobs jobs=null;
try {
connection=DBUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,job_id);
resultSet= preparedStatement.executeQuery();
if (resultSet.next()){
jobs=new Jobs();
String j_id=resultSet.getString("job_id");
String j_title=resultSet.getString("job_title");
int j_min=resultSet.getInt("min_salary");
int j_max=resultSet.getInt("max_salary");
jobs.setJob_id(j_id);
jobs.setJob_title(j_title);
jobs.setMin_salary(j_min);
jobs.setMax_salary(j_max);
}
return jobs;
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(connection,preparedStatement,resultSet);
}
return null;
}
public List<Jobs> selectAll(){
String sql ="select * from jobs;";
Connection connection = null;
PreparedStatement preparedstatement= null;
ResultSet resultSet=null;
Jobs jobs=null;
List<Jobs> jobsList=new ArrayList<>();;
try {
connection = DBUtils.getConnection();
preparedstatement = connection.prepareStatement(sql);
resultSet=preparedstatement.executeQuery();
while (resultSet.next()){
String j_id=resultSet.getString("job_id");
String j_title=resultSet.getString("job_title");
int j_min=resultSet.getInt("min_salary");
int j_max=resultSet.getInt("max_salary");
jobs=new Jobs(j_id,j_title,j_min,j_max);
jobsList.add(jobs);
}
return jobsList;
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(connection,preparedstatement,resultSet);
}
return null;
}
}#Date 工具类
java.sql.Date和java.util.Date不能通用，需要转换
##SimpleDateFormat
格式化和解析日期的工具类。允许进行格式化（日期 --> 文本）、解析（文本-->日期）和规范化。
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-mm-dd");
java.util.Date date=sdf.parse(String dateStr);
String dates=sdf.format(date);public class DateUtils {
private static final SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-mm-dd");
//字符串转Util
public static java.util.Date strToUtilDate(String str){
try {
return simpleDateFormat.parse(str);
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
//util转sql
public static java.sql.Date utilToSql(java.util.Date date){
return new java.sql.Date(date.getTime());
}
//util转字符串
public static String toStr(java.util.Date date){
return simpleDateFormat.format(date);
}
}
#Service 业务逻辑层
业务经常可以由一个或多个DAO的调用组成。
##编写service实现转账功能
#事务
在JDBC中，获得Connection对象开始事务--提交或回滚--关闭连接。其事务策略是
connection.setAutoCommit(false);//关闭自动回滚
connection.commit();
connection.rollback();##service层控制事务
会出现过个connection对象，避免这个问题就引出了ThreadLocal
#事务的封装
##完善工具类
public class DBUtils {
private static final Properties PROPERTIES=new Properties();
private static ThreadLocal<Connection> t1=new ThreadLocal<>();
static {
InputStream is=DBUtils.class.getResourceAsStream("/db.properties");
try {
PROPERTIES.load(is);
Class.forName(PROPERTIES.getProperty("driver"));
} catch (ClassNotFoundException | IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection connection= t1.get();
try {
if (connection ==null){
connection=DriverManager.getConnection(PROPERTIES.getProperty("url"), PROPERTIES.getProperty("username"), PROPERTIES.getProperty("password") );
t1.set(connection);
}
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//开启事务
public static void begin(){
Connection connection=getConnection();
try {
connection.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
}
//提交事务
public static void commit(){
Connection connection=getConnection();
try {
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(connection,null,null);
}
}
//回滚事务
public static void rollback(){
Connection connection=getConnection();
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(connection,null,null);
}
}
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null){
statement.close();
}
if (connection != null){
connection.close();
t1.remove();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}