java与数据库结合学习（java如何与数据库建立连接）

今天学习了如何连接数据库：

public class connectMySQL {

//连接数据库字符串

private static String url = "jdbc:mysql://202.116.54.30:3306/mydb?";

//jdbc协议+数据库协议+主机协议+短裤+连接数据库 //用户名

private static String user = "root";

private static String password = "root";

public static void main(String[] args) {

try {

//1.加载驱动

Class.forName("com.mysql.cj.jdbc.Driver");

//2.建立连接

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1?useSSL=false&serverTimezone=UTC","root","root");

//连接成后即可操作数据库，增删改查

System.out.println("连接成后，可操作数据库，增删改查");

//关闭连接

conn.close();

Thread.sleep(2000);

System.out.println("连接关闭");

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();}}}

public class connectOracle {

//连接数据库字符串

private static String url = "jdb:oracle:thin:@localhost:1521:orcl";

//jdbc协议+数据库协议+主机协议+短裤+连接数据库 //用户名

private static String user = "system";

private static String password = "root";

public static void main(String[] args) {

try {

//1.加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//2.建立连接

Connection conn = DriverManager.getConnection(url,user,password);

//连接成后即可操作数据库，增删改查

System.out.println("连接成后，可操作数据库，增删改查");

//关闭连接

conn.close();

Thread.sleep(2000);

System.out.println("连接关闭");

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();}}}

public class Demo01 {

//连接数据库字符串

private static String url = "jdbc:mysql://202.116.54.30:3306/mydb?";

//jdbc协议+数据库协议+主机协议+短裤+连接数据库 //用户名

private static String user = "root";

private static String password = "root";

public static void main(String[] args) {

//ShowAllEmp();

//ShowSingleEmp(7499);

//UserLogin();

//UserLogin2();

//UpdateEmp();

DeleteEmp();

}

private static void DeleteEmp() {

try {

//1.加载驱动

Class.forName("com.mysql.cj.jdbc.Driver");

//2.建立连接

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC","root","root");

//连接成后即可操作数据库，增删改查

Scanner input=new Scanner(System.in);

System.out.print("请输入要删除的员工编号:");

int empno=input.nextInt();

String sql="delete from emp where empno = ?";

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, empno);

int count = pstmt.executeUpdate();

if(count > 0) {

System.out.println("员工删除成功!");

}else {

System.out.println("员工删除失败!");

}

//关闭连接

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

}

private static void UpdateEmp() {

try {

//1.加载驱动

Class.forName("com.mysql.cj.jdbc.Driver");

//2.建立连接

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC","root","root");

//连接成后即可操作数据库，增删改查

Scanner input=new Scanner(System.in);

System.out.print("请输入员工编号:");

int empno=input.nextInt();

System.out.print("请输入员工姓名:");

String ename=input.next();

System.out.print("请输入员工 职位:");

String job=input.next();

System.out.print("请输入上司编号:");

int mgr=input.nextInt();

System.out.print("请输入入职日期(yyyy-MM-dd):");

String date=input.next();

SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");

Date hiredate=sdf.parse(date);

System.out.print("请输入基本工资:");

double sal=input.nextDouble();

System.out.print("请输入奖金:");

double comm=input.nextDouble();

System.out.print("请输入部门编号(10,20,30):");

int deptno=input.nextInt();

String sql="insert into emp values(?,?,?,?,?,?,?,?)";

System.out.println(sql);

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, empno);

pstmt.setString(2, ename);

pstmt.setString(3, job);

pstmt.setInt(4, mgr);

pstmt.setDate(5, new java.sql.Date(hiredate.getTime()));

pstmt.setDouble(6, sal);

pstmt.setDouble(7, comm);

pstmt.setInt(8, deptno);

int count = pstmt.executeUpdate();

if(count > 0) {

System.out.println("员工添加成功!");

}else {

System.out.println("员工添加错失败!");

}

//关闭连接

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

}

//使用Statement对象登录

private static void UserLogin2() {

try {

//1.加载驱动

Class.forName("com.mysql.cj.jdbc.Driver");

//2.建立连接

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC","root","root");

//连接成后即可操作数据库，增删改查

Scanner input=new Scanner(System.in);

System.out.print("请输入用户名:");

String uname=input.nextLine();

System.out.print("请输入密码:");

String pwd=input.nextLine();

String sql="select * from user where username='"+uname+"' and password='"+pwd+"'";

System.out.println(sql);

Statement pstmt = conn.createStatement();

ResultSet rs=pstmt.executeQuery(sql);

if(rs.next()) {

System.out.println("登录成功!");

}else {

System.out.println("用户名或密码错误!");

}

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

}

private static void UserLogin() {

try {

//1.加载驱动

Class.forName("com.mysql.cj.jdbc.Driver");

//2.建立连接

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC","root","root");

//连接成后即可操作数据库，增删改查

System.out.println("连接成后，可操作数据库，增删改查");

Scanner sc = new Scanner(System.in);

System.out.println("请输入用户名：");

String username = sc.nextLine();

System.out.println("请输入密码：");

String pwd = sc.nextLine();

//查询数据库，查找所有员工

//创建SQL语句字符串

String sql = "select * from User1 where username = ? and password = ?";

System.out.println(sql);

//定义发送SQL语句的运载工具PrepareStatement对象

PreparedStatement pstmt = conn.prepareStatement(sql);

//执行查询

pstmt.setString(1, username);

pstmt.setString(2, pwd);

ResultSet rs = pstmt.executeQuery();

//取值

//rs.next();//rs最初指向第0行，每调用一次next方法就往下移一行

if(rs.next()) {

System.out.println("登陆成功");

}else {

System.out.println("用户名或者密码输入错误");

}

//关闭连接

conn.close();

Thread.sleep(2000);

System.out.println("连接关闭");

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

private static void ShowSingleEmp(int eno) {

try {

//1.加载驱动

Class.forName("com.mysql.cj.jdbc.Driver");

//2.建立连接

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC","root","root");

//连接成后即可操作数据库，增删改查

System.out.println("连接成后，可操作数据库，增删改查");

//查询数据库，查找所有员工

//创建SQL语句字符串

String sql = "select * from emp where empno = ?";

System.out.println(sql);

//定义发送SQL语句的运载工具PrepareStatement对象

PreparedStatement pstmt = conn.prepareStatement(sql);

//执行查询

pstmt.setInt(1, eno);

ResultSet rs = pstmt.executeQuery();

//取值

//rs.next();//rs最初指向第0行，每调用一次next方法就往下移一行

if(rs.next()) {

//用列名

System.out.print("员工编号："+rs.getInt("empno"));

System.out.print("，姓名："+rs.getString("ename"));

System.out.print("，职位："+rs.getString("job"));

System.out.print("上司："+rs.getInt("mgr"));

System.out.print("入职日期："+rs.getDate("hiredate"));

System.out.print("基本工资："+rs.getDouble("sal"));

// //用列号

// System.out.println("员工编号："+rs.getInt(1));

// System.out.println("，姓名："+rs.getString(2));

// System.out.println("，职位："+rs.getString(3));

// System.out.println("上司："+rs.getInt(4));

// System.out.println("入职日期："+rs.getDate(5));

// System.out.println("基本工资："+rs.getDouble(6));

}

//关闭连接

conn.close();

Thread.sleep(2000);

System.out.println("连接关闭");

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

private static void ShowAllEmp() {

try {

//1.加载驱动

Class.forName("com.mysql.cj.jdbc.Driver");

//2.建立连接

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC","root","root");

//连接成后即可操作数据库，增删改查

System.out.println("连接成后，可操作数据库，增删改查");

//查询数据库，查找所有员工

//创建SQL语句字符串

String sql = "select * from emp";

//定义发送SQL语句的运载工具PrepareStatement对象

PreparedStatement pstmt = conn.prepareStatement(sql);

//执行查询

ResultSet rs = pstmt.executeQuery();

//取值

//rs.next();//rs最初指向第0行，每调用一次next方法就往下移一行

while(rs.next()) {

//用列名

// System.out.println("员工编号："+rs.getInt("empno"));

// System.out.println("，姓名："+rs.getString("ename"));

// System.out.println("，职位："+rs.getString("job"));

// System.out.println("上司："+rs.getInt("mgr"));

// System.out.println("入职日期："+rs.getDate("hiredate"));

// System.out.println("基本工资："+rs.getDouble("sal"));

//用列号

System.out.println("员工编号："+rs.getInt(1));

System.out.println("，姓名："+rs.getString(2));

System.out.println("，职位："+rs.getString(3));

System.out.println("上司："+rs.getInt(4));

System.out.println("入职日期："+rs.getDate(5));

System.out.println("基本工资："+rs.getDouble(6));

}

//关闭连接

conn.close();

Thread.sleep(2000);

System.out.println("连接关闭");

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}}}

具体导包文件省略，感觉连接起来跟IO流的理解运用差不多，就加上了MySQL和Oracle的语言基础