JavaWeb——JDBC

package jdbc; import java.sql.Connection; import java.sql.DriverManager;
import java.sql.Statement; public class demo { public static void main(String[]
args) throws Exception { //注册驱动 Class.forName("com.mysql.cj.jdbc.Driver");
//注册方法 String url = "jdbc:mysql://127.0.0.1:3306/itcast?" +
"useSSL=false&&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8";
String username = "root"; String password = "gyx.l123"; Connection conn =
DriverManager.getConnection(url, username, password); //定义SQL语句 String sql =
"insert into users values(4,'张六',30,'1','男',2)"; //获取statement对象 Statement sm =
conn.createStatement(); //执行sql int count = sm.executeUpdate(sql);//受影响的行数
//处理结果 System.out.println(count); //释放资源 sm.close(); conn.close(); } }

JDBC API详解

DriverManager

Connection

import java.sql.Connection; import java.sql.DriverManager; import
java.sql.Statement; public class demo2 { public static void main(String[] args)
throws Exception { //注册方法 String url = "jdbc:mysql://127.0.0.1:3306/itcast?" +
"useSSL=false&&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8";
String username = "root"; String password = "gyx.l123"; Connection conn =
DriverManager.getConnection(url, username, password); //定义SQL语句 String sql1 =
"update users set age = 30 where name = '张三'"; String sql2 = "update users set
age = 32 where name = '张四'"; //获取statement对象 Statement sm =
conn.createStatement(); try { //开启事务（手动提交） conn.setAutoCommit(false);
//执行sql1，并处理结果 int count1 = sm.executeUpdate(sql1);//受影响的行数
System.out.println(count1); //执行sql2，并处理结果 int count2 =
sm.executeUpdate(sql1);//受影响的行数 System.out.println(count1); //提交事务
conn.commit(); } catch (Exception e) { //回滚事务 conn.rollback(); } //释放资源
sm.close(); conn.close(); } }

Statement 

DDL语句不能用count的值判断是否执行成功，如"drop database username" DML语句可以用count的值判断是否执行成功

ResultSet

 
import java.sql.Connection; import java.sql.DriverManager; import
java.sql.ResultSet; import java.sql.Statement; public class demo_ResultSet {
public static void main(String[] args) throws Exception { //注册方法 String url =
"jdbc:mysql://127.0.0.1:3306/itcast?" +
"useSSL=false&&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8";
String username = "root"; String password = "gyx.l123"; Connection conn =
DriverManager.getConnection(url, username, password); //定义SQL语句
//DDL语句不能用count的值判断是否执行成功，如"drop database username" //DML语句可以用count的值判断是否执行成功
String sql = "select * from users"; //获取statement对象 Statement sm =
conn.createStatement(); //执行sql ResultSet res = sm.executeQuery(sql); //处理结果
while (res.next()){ int id = res.getInt(1); String name = res.getString(2); int
age = res.getInt(3); String gender = res.getString(4); int dep = res.getInt(5);
System.out.println(id+"\t"+name+"\t"+age+"\t"+gender+"\t"+dep); } //释放资源
res.close(); sm.close(); conn.close(); /* 1 张三 30 男 1 2 张四 28 男 2 3 张五 22 女 3 4
张六 30 男 2 5 王六 28 男 1 6 王五 28 男 1 */ } }
 
import pojo.users; import java.sql.Connection; import java.sql.DriverManager;
import java.sql.ResultSet; import java.sql.Statement; import
java.util.ArrayList; public class demo_ResultSet2 { public static void
main(String[] args) throws Exception { //注册方法 String url =
"jdbc:mysql://127.0.0.1:3306/itcast?" +
"useSSL=false&&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8";
String username = "root"; String password = "gyx.l123"; Connection conn =
DriverManager.getConnection(url, username, password); //定义SQL语句 String sql =
"select * from users"; //获取statement对象 Statement sm = conn.createStatement();
//执行sql ResultSet res = sm.executeQuery(sql); //处理结果 ArrayList<users> arr = new
ArrayList<users>(); while (res.next()){ //创建对象 users u =new users(); //获取数据 int
id = res.getInt(1); String name = res.getString(2); int age = res.getInt(3);
String gender = res.getString(4); int dep = res.getInt(5); //给对象赋值 u.setId(id);
u.setName(name); u.setAge(age); u.setGender(gender); u.setDep(dep); //把对象加入集合
arr.add(u); } //打印集合 System.out.println(arr); //释放资源 res.close(); sm.close();
conn.close(); } } public class users { private int id; private String name;
private int age; private String gender; private int dep; public int getId() {
return id; } public void setId(int id) { this.id = id; } public String
getName() { return name; } public void setName(String name) { this.name = name;
} public int getAge() { return age; } public void setAge(int age) { this.age =
age; } public String getGender() { return gender; } public void
setGender(String gendert) { this.gender = gendert; } public int getDep() {
return dep; } public void setDep(int dep) { this.dep = dep; } @Override public
String toString() { return "users{" + "id=" + id + ", name='" + name + '\'' +
", age=" + age + ", gendert='" + gender + '\'' + ", dep=" + dep + '}'; } }

PreparedStatement

 登录案例：

import java.sql.Connection; import java.sql.DriverManager; import
java.sql.ResultSet; import java.sql.Statement; public class
demo_PreparedStatement { public static void main(String[] args) throws
Exception { //注册方法 String url = "jdbc:mysql://127.0.0.1:3306/itcast?" +
"useSSL=false&&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8";
String username = "root"; String password = "gyx.l123"; Connection conn =
DriverManager.getConnection(url, username, password); //接收用户输入的用户名和密码 String
name = "小红"; String psw = "123"; //String name = "3326988"; //String psw = "'or
'1'='1"; //定义SQL语句 String sql = "select * from username_password where username
= '" + name + "' and password='" + psw + "'"; //获取statement对象 Statement sm =
conn.createStatement(); //执行sql ResultSet res = sm.executeQuery(sql);
//判断是否登陆成功 if(res.next()){//res有数据，返回true System.out.println("登陆成功"); }else{
System.out.println("登录失败"); } //释放资源 res.close(); sm.close(); conn.close(); } }

import java.sql.*; public class demo_PreparedStatement { public static void
main(String[] args) throws Exception { //注册方法 String url =
"jdbc:mysql://127.0.0.1:3306/itcast?" +
"useSSL=false&&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8";
String username = "root"; String password = "gyx.l123"; Connection conn =
DriverManager.getConnection(url, username, password); //接收用户输入的用户名和密码 String
name = "小红"; String psw = "123"; //定义SQL语句 String sql = "select * from
username_password where username = ? and password = ?"; //获取
PreparedStatement对象，并设置 ? 的值 PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, name); ps.setString(2, psw); //执行sql ResultSet res =
ps.executeQuery(); //判断是否登陆成功 if (res.next()) {//res有数据，返回true
System.out.println("登陆成功"); } else { System.out.println("登录失败"); } //释放资源
res.close(); ps.close(); conn.close(); } }

数据库连接池Druid

 

 

import com.alibaba.druid.pool.DruidDataSourceFactory; import
javax.sql.DataSource; import java.io.FileInputStream; import
java.sql.Connection; import java.util.Properties; public class druid_Demo {
public static void main(String[] args) throws Exception { //1.导jar包 //2.配置文件
//3.加载配置文件 Properties prop = new Properties(); prop.load(new
FileInputStream("jdbc-demo/src/druid.properties"));
//System.out.println(System.getProperty("user.dir"));找当前路径的方法 //4.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//5.获取数据库连接 Connection Connection conn = dataSource.getConnection();
System.out.println(conn); } }

• « 上一篇：初学者关于SQL条件判断函数---CASE WHEN、IF、IFNULL
• » 下一篇：单干必备：论嵌入式模块化编程、驱动分离的重要性