文章目录
JAVA JDBC连接MySQL数据库JDBC使用1、通过反射加载驱动2.建立连接3.创建执行器 用了执行sql语句createStatement()prepareStatement() 4.执行sql语句5.获取结果6.关闭 JDBC查询全体学生信息JDBC 添加学生信息JDBC 删除学生信息JDBC 修改学生信息 JDBC 用户登录JDBC用户登录1JDBC用户登录2 MySQLUtil 工具类JDBCUtilTestJAVA JDBC连接MySQL数据库
JDBC使用
1、通过反射加载驱动
Class.forName("com.mysql.jdbc.Driver");
2.建立连接
Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/shujia?useUnicode=true&characterEncoding=utf8","root","123456");
3.创建执行器 用了执行sql语句
createStatement()
prepareStatement()
Statement statement = connection.createStatement();String sql = "select * from student";
4.执行sql语句
ResultSet rs = statement.executeQuery(sql);
5.获取结果
while (rs.next()){System.out.print(rs.getInt("id") + "\t");System.out.print(rs.getString("name") + "\t");System.out.print(rs.getInt("age") + "\t");System.out.print(rs.getString("sex") + "\t");System.out.print(rs.getString("clazz") + "\t");System.out.print(rs.getString("job") + "\t");System.out.print(rs.getString("gongzi") + "\t\n");}
6.关闭
rs.close();statement.close();connection.close();
JDBC查询全体学生信息
import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;public class mysqlSelectDemo02 {public static void main(String[] args) throws Exception{//jdbc使用//1.通过反射加载 加载驱动Class.forName("com.mysql.jdbc.Driver");//2.建立连接Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/shujia?useUnicode=true&characterEncoding=utf8","root","123456");//3.创建执行器 用来执行SQL语句//3.1 createStatement()//3.2 prepareStatement()Statement statement = connection.createStatement();String sql = "select * from student";//4.执行sql语句ResultSet rs = statement.executeQuery(sql);//5.获取结果while (rs.next()){System.out.print(rs.getInt("id") + "\t");System.out.print(rs.getString("name") + "\t");System.out.print(rs.getInt("age") + "\t");System.out.print(rs.getString("sex") + "\t");System.out.print(rs.getString("clazz") + "\t");System.out.print(rs.getString("job") + "\t");System.out.print(rs.getString("gongzi") + "\t\n");}//6.关闭rs.close();statement.close();connection.close();}}
运行结果：1401刘生发25男14没有工作17k1402胡杰靓24男14大数据工程师18k1403朱佳乐23男14没有工作17k1404张咪 23女14没有工作17k1405杨旭22男14码农15k1406陶华根22男14码农15k1407潘磊22男14码农14k1408王友虎24男14大数据高级开发工程师20k1409覃笑26男14大数据架构师50k1410潘磊22男14码农14k1411王友虎24男14大数据高级开发工程师20k1412覃笑26男14大数据架构师50k1413潘磊22男14码农14k1414王友虎24男14大数据高级开发工程师20k1415覃笑26男14大数据架构师50k1416潘磊22男14码农14k1417王友虎24男14大数据高级开发工程师20k1418覃笑26男14大数据架构师50k1419王友虎24男14大数据高级开发工程师20k1420覃笑26男14大数据架构师50k1421覃笑26男14大数据高级开发工程师20k1422王友虎24男14大数据高级开发工程师20k1423覃笑26男14大数据架构师50k1424覃笑26男14大数据高级开发工程师20k1425王友虎24男14大数据高级开发工程师20k1426覃笑26男14大数据架构师50k1427覃笑26男14大数据高级开发工程师20k1428王友虎24男14大数据高级开发工程师20k1429覃笑26男14大数据架构师50k1430覃笑26男14大数据高级开发工程师20k1431李静25女14没有工作17k1432奥特曼25男14打怪兽 15k1433杨金杨25男14码农15k1434杨金杨25男14打怪兽15k1435杨金杨25男14打豆豆15k1436杨金杨25男14打怪兽15k1437李静 25女14打豆豆20k1438覃笑26男14招生25k1439李静27男14搞钱28k1440张志凯26男14铠甲勇士10k1441靓仔24男14大数据高级开发工程师20k
JDBC 添加学生信息
import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;import java.util.Scanner;public class mysqlInsert {public static void main(String[] args) throws Exception{Scanner scanner = new Scanner(System.in);System.out.println("请输入您的id:");int id = scanner.nextInt();System.out.println("请输入您的姓名:");String name = scanner.next();System.out.println("请输入您的年龄:");int age = scanner.nextInt();System.out.println("请输入您的性别:");String sex = scanner.next();System.out.println("请输入您的班级:");String clazz = scanner.next();System.out.println("请输入您想要的工作:");String job = scanner.next();System.out.println("请输入您想要的薪资:");String gongzi = scanner.next();Class.forName("com.mysql.jdbc.Driver");Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/shujia?useUnicode=true&characterEncoding=utf8","root","123456");Statement statement = connection.createStatement();String sql = "insert into student(id,name,age,sex,clazz,job,gongzi) values("+id+",'"+name+"',"+age+",'"+sex+"','"+clazz+"','"+job+"','"+gongzi+"')";int i = statement.executeUpdate(sql);if (i==i){System.out.println("注册成功，影响行数：" + i);}else {System.out.println("注册失败");}statement.close();connection.close();}}
运行结果：请输入您的id:1443请输入您的姓名:test请输入您的年龄:23请输入您的性别:女请输入您的班级:14请输入您想要的工作:划水请输入您想要的薪资:50k注册成功，影响行数：1
JDBC 删除学生信息
import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;public class mysqlDelete {public static void main(String[] args) throws Exception{Class.forName("com.mysql.jdbc.Driver");Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/shujia?useUnicode=true&characterEncoding=utf8","root","123456");Statement statement = connection.createStatement();String sql = "delete from student where name = 'test'";int i = statement.executeUpdate(sql);if (i>0){System.out.println("执行成功，影响行数：" + i);}else {System.out.println("执行失败，影响行数：" + i);}statement.close();connection.close();}}
运行结果：执行成功，影响行数：1
JDBC 修改学生信息
import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;public class mysqlUpdate {public static void main(String[] args) throws Exception{Class.forName("com.mysql.jdbc.Driver");Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/shujia?useUnicode=true&characterEncoding=utf8","root","123456");Statement statement = connection.createStatement();String sql = "update student set age = 80 where name = 'test'";int i = statement.executeUpdate(sql);if (i>0){System.out.println("执行成功，影响行数：" + i);}else {System.out.println("执行失败，影响行数：" + i);}statement.close();connection.close();}}
运行结果：执行成功，影响行数：1
JDBC 用户登录
JDBC用户登录1
import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;import java.util.Scanner;public class login01 {public static void main(String[] args) throws Exception{Scanner scanner = new Scanner(System.in);System.out.println("请输入用户:");String username = scanner.next();System.out.println("请输入密码:");String password = scanner.next();Class.forName("com.mysql.jdbc.Driver");Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/shujia","root","123456");Statement statement = connection.createStatement();//1.通过username去mysql中查找有没有这一条记录(存在：输入密码，不存在:就报错了)//2.存在之后 用查到的密码匹配输入的密码String sql = "select * from user where username='"+username+"'";ResultSet rs = statement.executeQuery(sql);if (!rs.next()){System.out.println("用户输入错误");//结束}//匹配密码String password1 = rs.getString("password");if (password==null || !password.equals(password1)){System.out.println("密码不匹配");}System.out.println("登录成功");rs.close();statement.close();connection.close();}}
运行结果1：请输入用户:123请输入密码:456用户输入错误运行结果2：请输入用户:123请输入密码:456用户输入错误运行结果3：请输入用户:root请输入密码:123456登录成功
但是这种方法通过SQL注入 123’ or '1=1 是可以登录进去的（不安全）
JDBC用户登录2
import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.Scanner;public class login02 {public static void main(String[] args) throws Exception{Scanner scanner = new Scanner(System.in);System.out.println("请输入用户:");String username = scanner.next();System.out.println("请输入密码:");String password = scanner.next();Class.forName("com.mysql.jdbc.Driver");Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/shujia","root","123456");String sql = "select * from user where username=?";PreparedStatement statement = connection.prepareStatement(sql);statement.setString(1,username);ResultSet rs = statement.executeQuery();if (!rs.next()){System.out.println("用户输入错误");}//匹配密码String password1 = rs.getString("password");if (password==null || !password.equals(password1)){System.out.println("密码不匹配");}System.out.println("登录成功");rs.close();statement.close();connection.close();}}
这种方式是安全的，以后开发都是使用该方法
MySQLUtil 工具类
如果说项目多了，每次都要启动JDBC的驱动，太啰嗦了为了减少工作量，我们自己定义一个MySQL JDBC工具类
import java.sql.*;public class MysqUtil {static String DRIVER="com.mysql.jdbc.Driver";static String URL="jdbc:mysql://master:3306/shujia?useUnicode=true&characterEncoding=utf8";static String USERNAME="root";static String PASSWORD="123456";static Connection connection = null;static PreparedStatement ps = null;static ResultSet rs = null;static {try {Class.forName(DRIVER);} catch (ClassNotFoundException e) {e.printStackTrace();}}public static Connection getConn() {try {connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);} catch (SQLException e) {e.printStackTrace();}return connection;}public static PreparedStatement getPs(String sql){try {ps = connection.prepareStatement(sql);} catch (SQLException e) {e.printStackTrace();}return ps;}//查public static ResultSet getRs(){try {rs = ps.executeQuery();} catch (SQLException e) {e.printStackTrace();}return rs;}//增删改public static int getInsert(){int i = 0;try {i = ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();}return i;}public static void close(){if (rs!=null){try {rs.close();} catch (SQLException e) {e.printStackTrace();}if (ps!=null){try {ps.close();} catch (SQLException e) {e.printStackTrace();}if (connection!=null){try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}}}}
使用该方法，以后想更改服务器，更改数据库，地址变了没关系，直接修改一下即可
JDBCUtilTest
import mysql.util.MysqUtil;import java.sql.PreparedStatement;public class JdbcUtilTest {public static void main(String[] args) {MysqUtil.getConn();String sql = "insert into student(id,name,age,sex,clazz,job,gongzi) values(1445,'test',23,'nan','14','扫厕所','3k')";PreparedStatement ps = MysqUtil.getPs(sql);int insert = MysqUtil.getInsert();if (insert>0){System.out.println("运行成功，影响行数" + insert);}else {System.out.println("运行失败，影响行数" + insert);}MysqUtil.close();}}
运行结果：运行成功，影响行数1
🆗到底啦给靓仔一个关注吧！
