本日学习内容,idea的jar包添加,数据库连接与关闭
package cn.tedu.jbdc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
//利用将jbdc,操作数据库
//需求:查询部门表的所有数据
public class tTest1 {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取数据库的连接
//String url="协议://数据库的服务器的IP地址:端口号/数据库名";
String url="jdbc:mysql://localhost:3306/cgb2111";
Connection c = DriverManager.getConnection(url,"root","root");
//3.获取传输器
Statement s = c.createStatement();
//4.执行sql
ResultSet r = s.executeQuery("select * from dept");//执行查询的sql
System.out.println("java程序与数据库连接成功!!!");
//5.处理结果
while(r.next()){
int deptno = r.getInt(1);
String dname = r.getString(2);
String loc = r.getString(3);
System.out.println(deptno+"t"+dname+"t"+loc);
}
//6.关闭资源
r.close();//关闭结果集ResultSet
s.close();//关闭传输器Statement
c.close();//关闭连接Connection
}
}
package cn.tedu.jbdc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Test2 {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/cgb2111?characterEncoding=utf8";
Connection c = DriverManager.getConnection(url,"root","root");
Statement s = c.createStatement();
ResultSet r = s.executeQuery("select * from user");
while(r.next()){
int id = r.getInt(1);
String name = r.getString(2);
String pwg = r.getString(3);
System.out.println(id+"t"+name+"t"+pwg);
}
r.close();
s.close();
c.close();
}
}
创建连接通道工具类
public class JDBCUtils {
static public Connection get() throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/cgb2111?characterEncoding=utf8";
Connection c = DriverManager.getConnection(url,"root","root");
return c;
}
}
连接通道关闭的完善
package cn.tedu.jbdc;
import java.sql.*;
public class Test3 {
public static void main(String[] args) {
Connection c = null;
Statement s = null;
ResultSet r = null;
try {
c = JDBCUtils.get();
s = c.createStatement();
s.executeUpdate("insert into user values(null,'司马懿','54544')");
r = s.executeQuery("select * from user ");
while (r.next()) {
int a = r.getInt(1);
String b = r.getString(2);
String d = r.getString(3);
System.out.println(a + "t" + b + "t" + d);
}
}catch(Exception e){
System.out.println("数据有误");
}finally {
if(r!=null){
try {
r.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(s!=null){
try {
s.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(c!=null){
try {
c.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
在工具类中添加连接通道的关闭方法
package cn.tedu.jbdc;
import java.sql.*;
public class JDBCUtils {
static public Connection get() throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/cgb2111?characterEncoding=utf8";
Connection c = DriverManager.getConnection(url,"root","root");
return c;
}
static public void close(ResultSet r,PreparedStatement s,Connection c){
if (r!=null){
try {
r.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {//就是怕close()执行失败导致发生了异常,进行了catch
r = null;//手动置空,等着GC进行垃圾回收
}
}
if(s!=null){
try {
s.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
s = null;
}
}
if (c!=null){
try {
c.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
c = null;
}
}
}
}
测试工具类
package cn.tedu.jbdc;
import java.sql.*;
import java.util.Scanner;
public class Test4 {
public static void main(String[] args) {
Connection c = null;
PreparedStatement s = null;
ResultSet r = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/cgb2111?characterEncoding=utf8";
c = DriverManager.getConnection(url, "root", "root");
// Statement s = c.createStatement();
// String sql = "select * from user where name='"+name+"'and pwd='"+pwd+"'";
System.out.println("请输入用户名");
String name = new Scanner(System.in).nextLine();
System.out.println("请输入密码");
String pwd = new Scanner(System.in).nextLine();
String sql = "select * from user where name=? and pwd=?";
//String sql = "select * from user where name='曹操'and pwd='2321'";
s = c.prepareStatement(sql);
s.setString(1, name);
s.setString(2, pwd);
r = s.executeQuery();
if (r.next()) {
System.out.println("登录成功");
} else {
System.out.println("您输入的用户信息有误,请重新输入");
}
}catch(Exception e){
System.out.println("警告!警告!!程序有误!!!");
}finally {
JDBCUtils.close(r,s,c);
}
}
}
package cn.tedu.jbdc;
import java.sql.*;
public class Test5 {
public static void main(String[] args) {
// Class.forName("com.mysql.cj.jdbc.Driver");
// String url = "jdbc:mysql://localhost:3306/cgb2111?characterEncoding=utf8";
// Connection c = DriverManager.getConnection(url,"root","root");
Connection c = null;
PreparedStatement s = null;
ResultSet r = null;
try {
c = JDBCUtils.get();
String sql = "select * from dept where deptno=?";
s = c.prepareStatement(sql);
s.setObject(1, 1);
r = s.executeQuery();
while (r.next()) {
Object a = r.getObject(1);
String b = r.getString(2);
String d = r.getString(3);
System.out.println(a + "t" + b + "t" + d);
}
}catch (Exception e){
System.out.println("检索失败");
}finally {
JDBCUtils.close(r,s,c);
}
}
}
package cn.tedu.jbdc;
import java.sql.*;
public class Test6 {
public static void main(String[] args) {
Connection c = null;
PreparedStatement s = null;
try {
c = JDBCUtils.get();
String sql = "insert into user values(null,?,?)";
s = c.prepareStatement(sql);
s.setObject(1, "司马徵");
s.setObject(2, "5155");
s.executeUpdate();
System.out.println("存入成功");
}catch (Exception e){
System.out.println("存入失败");
}finally {
JDBCUtils.close(null,s,c);
}
}
}
