一引入jar包
二 实际练习
package day01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/*
* JDBC是SUN提供的一套用于操作数据库的标准接口,不同的数据库厂商都提供了
* 一套JDBC接口的实现,打成一个jar包发布,这个包叫驱动包
*
* JDBC接口中提供了
* DiverManager:加载驱动并负责与数据库连接
* Connection:表示与数据库的来接,管理事务,创建执行SQL语句的对象Statement
* Statement:用来执行SQL语句,若执行的是查询语句,会得到查询结果集ResultSet
* ResultSet:用于查询的结果集,遍历结果集可获取查询的具体数据
*/
public class JDBCDemo1 {
public static void main(String[] args) {
Connection conn=null;
try {
//1.加载驱动包,不同的数据库参数值不一样
Class.forName("com.mysql.jdbc.Driver");
//2.建立连接,不同的数据库有不同的连接格式
//例如:MySQL数据库地址 jdbc:mysql://数据库IP地址
String url="jdbc:mysql://localhost:3306/store_ykt";
//3.数据库连接账号
String username="root";
//4.数据库连接密码
String password="123456";
//DriverManager提供了连接数据库的方法
//getConnection(url,user,password)返回Connection;
conn = DriverManager.getConnection(url, username, password);
System.out.println("建立连接");
//通过Connection创建用于执行SQL语句的Statement实例
Statement stat = conn.createStatement();
String sql="create table myjdbc(id int primary key auto_increment,user_name varchar(10),password varchar(10))";
/*
*Statement执行SQL语句的相关方法
* int executeUpdate(String sql)(插,修改),若返回1则执行成功,返回0执行失败
* 它是专门用来执行DML语句(主要负责增删改查)
* resultSet excecuteQuery(String sql)(查询)专门来执行DQL语句,返回查询的结果集,
* 用resultSet实例返回
*
* boolean execute(String sql)可以执行任何类型的SQL语句,但由于DML,DQL都是
* 专门的方法执行,该方法一般用来执行DDL(创建)语句,返回值为执行后是否有结果集
*/
//用Statement执行语句
stat.execute(sql);
System.out.println("创建完毕");
} catch (Exception e) {
e.printStackTrace();
} finally {
//释放资源
try {
conn.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
package day01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/**
* 给myjdbc表中插入相关数据
*/
public class JDBCDemo2 {
public static void main(String[] args) {
Connection conn=null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/store_ykt";
String username="root";
String password="123456";
//获取连接
conn = DriverManager.getConnection(url, username, password);
//通过Connection连接获取执行SQL的实例Statement
Statement stat = conn.createStatement();
/*
* 要求用户输入用户名,密码,性别插入到表myjdbc表中
*/
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名");
String name = scanner.next();
System.out.println("请输入用户密码");
String pwd = scanner.next();
System.out.println("请输入用户性别");
char gender = scanner.next().charAt(0);
String sql = "insert into myjdbc(user_name,password,gender) values('"+name+"','"+pwd+"','"+gender+"')";
int n=stat.executeUpdate(sql);
if(n>0){
System.out.println("插入成功");
} else{
throw new RuntimeException( "插入失败");
}
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
package day01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/**
* 根据id删除用户数据
* @author 臻冉
*
*/
public class JDBCDemo3 {
public static void main(String[] args) {
Connection conn = null;
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.建立连接
String url = "jdbc:mysql://192.168.3.37:3306/store_ykt";
//3.连接账号
String username="root";
//4.连接密码
String password="123456";
conn = DriverManager.getConnection(url, username, password);
Statement stat = conn.createStatement();
Scanner scanner = new Scanner(System.in);
System.out.println("请输入id");
String id = scanner.next();
String sql = "delete from myjdbc where id="+id;
int n=stat.executeUpdate(sql);
if(n>0){
System.out.println("删除成功");
} else{
throw new RuntimeException( "删除失败");
}
} catch (Exception e) {
e.printStackTrace();
} finally{
//释放资源
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package day01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/**
* 根据id修改用户密码
* @author 臻冉
*
*/
public class JDBCDemo4 {
public static void main(String[] args) {
Connection conn=null;
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.建立连接
String url = "jdbc:mysql://192.168.3.37:3306/store_ykt";
//3.连接账号
String username = "root";
//4.连接密码
String password = "123456";
//5.连接数据库
conn = DriverManager.getConnection(url, username, password);
//6.执行sql语句的内容
Statement stat = conn.createStatement();
Scanner scanner = new Scanner(System.in);
System.out.println("请输入ID");
int id=scanner.nextInt();
System.out.println("请输入密码");
String pwd = scanner.next();
String sql = "update myjdbc set password='"+pwd+"' where id="+id;
//用Statement语句来执行SQL语句
int n = stat.executeUpdate(sql);
if(n>0){
System.out.println("修改成功");
} else {
throw new RuntimeException("修改失败");
}
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
//释放资源
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package day01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 查询数据
* ResultSet:表示查询的结果集,遍历结果集可获取查询的具体数据
*
* ResultSet executeQuery(String sql)(查询)专门用来执行DQL语句,返回查询的结果集
* 用ResultSet实例返回
* @author lenovo
*
*/
public class JDBCDemo5 {
public static void main(String[] args) {
Connection conn=null;
try {
//加载驱动包
Class.forName("com.mysql.jdbc.Driver");
//数据库地址
String url="jdbc:mysql://localhost:3306/store_ykt";
//数据库账号
String user="root";
//数据库密码
String password="123";
//建立连接
conn=DriverManager.getConnection(url, user, password);
//创建实例
Statement stat=conn.createStatement();
String sql="select * from myjdbc";
//执行查询结果返回结果集ResultSet(处理结果集)
ResultSet rs=stat.executeQuery(sql);
//见到ResultSet 用while查询数据,若判断用if
/*
* ResultSet提供了方法re.next()判断每行元素有没有数据
* ResultSet提供了getXX(int column):根据数据库字段的列获取数据
* ResultSet提供了getXX(String column):根据数据库字段的字段名列获取数据(推荐)
*/
while(rs.next()){
//根据数据库字段名获取字段名列的数据
int id=rs.getInt("id");
String name=rs.getString("user_name");
String pwd=rs.getString("password");
char gender=rs.getString("gender").charAt(0);
System.out.println(id+","+name+","+pwd+","+gender);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(conn!=null){//如果conn连接上了就释放资源,优化性能
conn.close();//释放资源
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package day01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/**
* 根据用户名和密码判断用户登录是否成功
* @author lenovo
*
*/
public class JDBCDemo6 {
public static void main(String[] args) {
Connection conn=null;
try {
//加载驱动包
Class.forName("com.mysql.jdbc.Driver");
//数据库地址
String url="jdbc:mysql://localhost:3306/store_ykt";
//数据库账号
String user="root";
//数据库密码
String password="123";
//建立连接
conn=DriverManager.getConnection(url, user, password);
System.out.println("建立连接");
//创建Statement对象实例执行sql语句
Statement stat=conn.createStatement();
Scanner scanner=new Scanner(System.in);
System.out.println("请输入账号");
String username=scanner.next();
System.out.println("请输入密码");
String pwd=scanner.next();
String sql="select * from myjdbc where user_name='"+username+"' and password='"+pwd+"'";
ResultSet rs=stat.executeQuery(sql);
if(rs.next()){
System.out.println("登录成功!");
}else{
throw new RuntimeException("账号或密码错误!");
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
try {
if(conn!=null){//如果conn连接上了就释放资源,优化性能
conn.close();//释放资源
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
package day01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/**
* 根据用户输入的员工编号查询出员工的名字,员工领导
* 员工部门及其部门名称和地址
*/
public class JDBCDemo7 {
public static void main(String[] args) {
Connection conn=null;
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.建立连接
String url = "jdbc:mysql://192.168.3.37:3306/store_ykt";
//3.连接账号
String username = "root";
//4.连接密码
String password = "123456";
//5.连接数据库
conn = DriverManager.getConnection(url, username, password);
//6.执行sql语句的内容
Statement stat = conn.createStatement();
Scanner scanner = new Scanner(System.in);
System.out.println("请输入员工编号");
int user_empno=scanner.nextInt();
String sql = "SELECT e.ename,e.empno,e.mgr,d.dname,d.loc FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno WHERE e.empno="+user_empno;
ResultSet rs = stat.executeQuery(sql);
while(rs.next()){
String ename = rs.getString("e.ename");
int empno = rs.getInt("e.empno");
int mgr = rs.getInt("e.mgr");
String dname = rs.getNString("d.dname");
String loc = rs.getString("d.loc");
System.out.println(ename+","+empno+","+mgr+","+dname+","+loc);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package day01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
/**
* 修改用户密码,前提是用户登录成功后方可修改
* @author 臻冉
*
*/
public class JDBCDemo8 {
public static void main(String[] args) {
Connection conn = null;
try {
//加载驱动包
Class.forName("com.mysql.jdbc.Driver");
//数据库地址
String url="jdbc:mysql://localhost:3306/store_ykt";
//数据库账号
String user="root";
//数据库密码
String password="123";
conn = DriverManager.getConnection(url);
Statement stat = conn.createStatement();
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名");
String username = scanner.nextLine();
System.out.println("请输入用户密码");
String userpwd = scanner.next();
String sql="select * from myjdbc where user_name='"+username+"'and password='"+userpwd+"'";
ResultSet rs = stat.executeQuery(sql);
if(rs.next()){
System.out.println("登录成功");
System.out.println("请输入新密码");
String newPwd = scanner.next();
String sql2="update myjdbc set password='"+newPwd+"' where user_name='"+username+"'";
int n = stat.executeUpdate(sql2);
if(n>0){
System.out.println("修改成功");
} else {
throw new RuntimeException("修改失败");
}
} else {
throw new RuntimeException("账号或密码错误");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(conn!=null){
conn.close();
}
} catch (Exception e2) {
// TODO: handle exception
}
}
}
}
package day01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/**
* 每页显示4条数据,要求员工信息按照工资的降序排列后
* 显示员工表中的第三页的员工编号,姓名,部门
* @author 臻冉
*
*/
public class JDBCDemo9 {
public static void main(String[] args) {
Connection conn = null;
try {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入每页显示的条目数");
int pageSize = scanner.nextInt();
System.out.println("请输入页数");
int page = scanner.nextInt();
//起始页(页数-1)*(每页显示的条数)
int start=(page-1)*pageSize;
//加载驱动包
Class.forName("com.mysql.jdbc.Driver");
//数据库地址
String url="jdbc:mysql://localhost:3306/store_ykt";
//数据库账号
String user="root";
//数据库密码
String password="123";
//建立连接获取Connection
conn = DriverManager.getConnection(url);
//获取执行sql语句的对象Statement
Statement stat = conn.createStatement();
String sql = "select *from emp order by sal desc limit "+start+","+pageSize;
ResultSet rs = stat.executeQuery(sql);
while(rs.next()){
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
int deptno = rs.getInt("deptno");
System.out.println(empno+","+ename+","+deptno);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}