JDBC

最新推荐文章于 2024-09-13 22:15:00 发布
最新推荐文章于 2024-09-13 22:15:00 发布
阅读量401 收藏
点赞数
文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_51239196/article/details/122046063
版权

JDBC操作步骤

package Demo;

import java.sql.*;

public class DEMO1 {

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet= null;

        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="123456";
            String url="jdbc:mysql://localhost:3306/yph2?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3. 定义sql,创建状态通道（进行sql语句的发送）
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from emp1");//executeQuery(sql) 执行查询
//4. 取出结果集信息
            while (resultSet.next()){//判断是否有下一条数据
                //取出数据 ：resultSet.getxxx("列名"); xxx表示数据类型
                System.out.println("姓名："+resultSet.getString("ename")+",工资："+resultSet.getDouble("sal")
                        +",雇佣日期："+resultSet.getDate("hiredate"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5. 关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    }
}

在java中进行增删改

package Demo;

import java.sql.*;

public class DEMO2 {

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;


        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="123456";
            String url="jdbc:mysql://localhost:3306/yph2?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3. 定义sql,创建状态通道（进行sql语句的发送）
            statement = connection.createStatement();
            // 返回结果为受影响的行数
            int result = statement.executeUpdate("insert into emp1(ename,hiredate,sal,empno) values('aa','2020-1-1',2000,1008)");
            if (result>0){
                System.out.println("执行成功");
            }else {
                System.out.println("执行失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5. 关闭资源
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    }
}

预状态通道

package Demo;

import java.sql.*;

public class DEMO3 {

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement pps =null;
        ResultSet resultSet= null;

        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="123456";
            String url="jdbc:mysql://localhost:3306/yph2?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3. 定义sql,创建预状态通道（进行sql语句的发送）
            String sql = "select * from emp1 where ename=? and sal=?";
            pps = connection.prepareStatement(sql);
            String ename="张三";
            int sal= 1000;
            //给占位符赋值（下标,内容）从1开始
            pps.setString(1,ename);
            pps.setInt(2,sal);
            //执行sql
            resultSet = pps.executeQuery();
            if (resultSet.next()){
                System.out.println("登陆成功！");
            }else {
                System.out.println("登录失败！");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5. 关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    }
}

对比statement和PreparedStatement;

(1)statement属于状态通道，PreparedStatement属于预状态通道

(2)预状态通道会先编译sql语句,再去执行，比statement执行效率高

(3)预状态通道支持占位符?,给占位符赋值的时候，位置从1开始

(4)预状态通道可以防止sql注入，原因:预状态通道在处理值的时候以字符串的方式处理

多表关系多对一

--创建数据表
CREATE TABLE `student` ( `stuid` int(11) NOT NULL AUTO_INCREMENT, `stuname` varchar(255) DEFAULT NULL, `teacherid` int(11) DEFAULT NULL, PRIMARY KEY (`stuid`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;INSERT INTO `student` VALUES ('1', 'aaa', '3');INSERT INTO `student` VALUES ('2', 'bb', '1');INSERT INTO `student` VALUES ('3', 'cc', '3');INSERT INTO `student` VALUES ('4', 'dd', '1');INSERT INTO `student` VALUES ('5', 'ee', '1');INSERT INTO `student` VALUES ('6', 'ff', '2');DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `tname` varchar(255) DEFAULT NULL, PRIMARY KEY (`tid`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;INSERT INTO `teacher` VALUES ('1', '张三老师');INSERT INTO `teacher` VALUES ('2', '李四老师');INSERT INTO `teacher` VALUES ('3', '王五');
package bean;

//多方
public class Student {
    private int stuid;
    private String stuname;
    private int teacherid;

    public int getStuid() {
        return stuid;
    }

    public void setStuid(int stuid) {
        this.stuid = stuid;
    }

    public String getStuname() {
        return stuname;
    }

    public void setStuname(String stuname) {
        this.stuname = stuname;
    }

    public int getTeacherid() {
        return teacherid;
    }

    public void setTeacherid(int teacherid) {
        this.teacherid = teacherid;
    }
}

package bean;

import java.util.List;

//一方
public class Teacher {
    private int tid;
    private String tname;
    //在一方创建存储多方数据的集合
    private List<Student> studentList;

    public int getTid() {
        return tid;
    }

    public void setTid(int tid) {
        this.tid = tid;
    }

    public String getTname() {
        return tname;
    }

    public void setTname(String tname) {
        this.tname = tname;
    }

    public List<Student> getStudentList() {
        return studentList;
    }

    public void setStudentList(List<Student> studentList) {
        this.studentList = studentList;
    }
}

package dao;

import bean.Teacher;

public interface TeacherDao {
    //定义操作方法
    //1.定义一个根据老师ID查询老师信息（学生信息）
    public Teacher getById(int tid);
}

package dao.impl;

import bean.Student;
import bean.Teacher;
import dao.TeacherDao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TeacherDaoImpl implements TeacherDao {
    @Override
    public Teacher getById(int tid) {
        //操作数据库
        Connection connection = null;
        PreparedStatement pps =null;
        ResultSet resultSet= null;

        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="123456";
            String url="jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3. 定义sql,创建预状态通道（进行sql语句的发送）
            String sql = "SELECT * FROM student S,teacher t WHERE s.teacherid=t.tid AND t.tid=?";
            pps = connection.prepareStatement(sql);

            //给占位符赋值（下标,内容）从1开始

            pps.setInt(1,tid);
            //执行sql
            resultSet = pps.executeQuery();
            Teacher teacher = new Teacher();
            List<Student> students = new ArrayList<Student>();
            while (resultSet.next()){
                //1.取出各自的信息
                teacher.setTid(resultSet.getInt("stuid"));
                teacher.setTname(resultSet.getString("tname"));

                Student student =new Student();
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));

                //2.建立老师和学生之间的联系
                students.add(student);
            }
            teacher.setStudentList(students);
            return teacher;
        }catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5. 关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
}

测试

package test;

import bean.Student;
import bean.Teacher;
import dao.TeacherDao;
import dao.impl.TeacherDaoImpl;

import java.util.List;

public class Demo {
    public static void main(String[] args) {
        TeacherDao dao = new TeacherDaoImpl();
        Teacher teacher = dao.getById(1);
        System.out.println("老师姓名："+teacher.getTname());
        List<Student> studentList = teacher.getStudentList();
        for (Student student:studentList) {
            System.out.println("\t studentName="+student.getStuname());
        }
    }
}

多对一

//在上方studen类中多加一个对象teacher
//多对一：是在多方创建一个存储一方数据的对象
    private Teacher teacher;

//在接口中加
//查询所有的学生（包含老师的信息）
    public List<Student> getAll();
@Override
    public List<Student> getAll() {
        //操作数据库
        Connection connection = null;
        PreparedStatement pps =null;
        ResultSet resultSet= null;

        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="123456";
            String url="jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3. 定义sql,创建预状态通道（进行sql语句的发送）
            String sql = "SELECT * FROM student S,teacher t WHERE s.teacherid=t.tid";
            pps = connection.prepareStatement(sql);
            //执行sql
            resultSet = pps.executeQuery();
            List<Student> students = new ArrayList<>();
            while (resultSet.next()){
                //1.取出各自的信息
                Student student =new Student();
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));

                Teacher teacher = new Teacher();
                teacher.setTid(resultSet.getInt("tid"));
                teacher.setTname(resultSet.getString("tname"));
                //2.建立老师和学生之间的联系
                student.setTeacher(teacher);
                students.add(student);
            }

            return students;
        }catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5. 关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
//测试
        TeacherDao dao = new TeacherDaoImpl();
        List<Student> students = dao.getAll();
        for (Student student:students) {
            System.out.println(student.getStuname()+","+student.getTeacher().getTname());
        }

多表关系双向1对1

package bean;

public class Wife {
    private int wifeId;
    private String wifeName;
    private int hid;
    private  Husband husband;

    public int getWifeId() {
        return wifeId;
    }

    public void setWifeId(int wifeId) {
        this.wifeId = wifeId;
    }

    public String getWifeName() {
        return wifeName;
    }

    public void setWifeName(String wifrName) {
        this.wifeName = wifrName;
    }

    public int getHid() {
        return hid;
    }

    public void setHid(int hid) {
        this.hid = hid;
    }

    public Husband getHusband() {
        return husband;
    }

    public void setHusband(Husband husband) {
        this.husband = husband;
    }
}

package bean;

public class Husband {
    private int husId;
    private String husName;
    private Wife wife;

    public int getHusId() {
        return husId;
    }

    public void setHusId(int husId) {
        this.husId = husId;
    }

    public String getHusName() {
        return husName;
    }

    public void setHusName(String husName) {
        this.husName = husName;
    }

    public Wife getWife() {
        return wife;
    }

    public void setWife(Wife wife) {
        this.wife = wife;
    }
}

package dao;

import bean.Husband;
import bean.Wife;

public interface WifeDao {
    //查询妻子信息（包含丈夫信息）
    public Wife getWife(int wid);
    //查询丈夫信息（包含妻子信息）
    public Husband getHusband(int hid);
}

package dao.impl;

import bean.Husband;
import bean.Student;
import bean.Teacher;
import bean.Wife;
import dao.WifeDao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class WifeDaoImpl implements WifeDao {
    @Override
    public Wife getWife(int wid) {
        //操作数据库
        Connection connection = null;
        PreparedStatement pps =null;
        ResultSet resultSet= null;

        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="123456";
            String url="jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3. 定义sql,创建预状态通道（进行sql语句的发送）
            String sql = "SELECT * FROM wife w,husband h WHERE w.hid=h.husid AND w.wifeid=?";
            pps = connection.prepareStatement(sql);
            //给占位符赋值（下标,内容）从1开始
            pps.setInt(1,wid);
            //执行sql
            resultSet = pps.executeQuery();
            Wife wife = new Wife();

            while (resultSet.next()){
                //1.取出各自的信息
                wife.setWifeId(resultSet.getInt("wifeid"));
                wife.setWifeName(resultSet.getString("wifename"));

                Husband husband = new Husband();
                husband.setHusId(resultSet.getInt("husid"));
                husband.setHusName(resultSet.getString("husname"));

                //2.建立妻子和丈夫之间的联系
                wife.setHusband(husband);
            }
            return wife;
        }catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5. 关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public Husband getHusband(int hid) {
        //操作数据库
        Connection connection = null;
        PreparedStatement pps =null;
        ResultSet resultSet= null;

        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="123456";
            String url="jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3. 定义sql,创建预状态通道（进行sql语句的发送）
            String sql = "SELECT * FROM wife w,husband h WHERE w.hid=h.husid AND h.husid=?";
            pps = connection.prepareStatement(sql);
            //给占位符赋值（下标,内容）从1开始
            pps.setInt(1,hid);
            //执行sql
            resultSet = pps.executeQuery();
            Husband husband = new Husband();

            while (resultSet.next()){
                //1.取出各自的信息
                Wife wife = new Wife();
                wife.setWifeId(resultSet.getInt("wifeid"));
                wife.setWifeName(resultSet.getString("wifename"));


                husband.setHusId(resultSet.getInt("husid"));
                husband.setHusName(resultSet.getString("husname"));

                //2.建立妻子和丈夫之间的联系
                husband.setWife(wife);
            }
            return husband;
        }catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5. 关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
}

测试

package test;

import bean.Husband;
import bean.Wife;
import dao.impl.WifeDaoImpl;

public class Demo2 {
    public static void main(String[] args) {
        WifeDaoImpl wifeDao = new WifeDaoImpl();
        Wife wife = wifeDao.getWife(1);
        System.out.println(wife.getWifeName()+","+wife.getHusband().getHusName());
        Husband husband = wifeDao.getHusband(1);
        System.out.println(husband.getHusName()+","+husband.getWife().getWifeName());
    }
}

事务

//手动提交事务
connection.setAutoCommit(false);
//代码方式提交事务
connection.commit();
//设置保存点
savepoint = connection.setSavepoint("abc");
try {
    //回滚
    //connection.rollback();
    //回滚到保存点savepoint的位置
    connection.rollback(savepoint);
    //结束事务
    connection.commit();
} catch (SQLException throwables) {
    throwables.printStackTrace();
}
package Demo;

import java.sql.*;

public class DEMO4 {

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        Savepoint savepoint =null;


        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="123456";
            String url="jdbc:mysql://localhost:3306/yph2?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //手动提交事务
            connection.setAutoCommit(false);
            //3. 定义sql,创建状态通道（进行sql语句的发送）
            statement = connection.createStatement();
            // 返回结果为受影响的行数
            int result= statement.executeUpdate("insert into emp1(ename,hiredate,sal,empno) values('张三a','2020-1-1',2000,1008)");
            //设置保存点
            savepoint = connection.setSavepoint("abc");
            int result2 = statement.executeUpdate("insert into emp1(ename,hiredate,sal,empno) values('张三b','2020-1-1',2000,1008)");
            System.out.println(6/0);
            //代码方式提交事务
            connection.commit();
            if (result>0){
                System.out.println("执行成功");
            }else {
                System.out.println("执行失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            try {
                //回滚
                //connection.rollback();
                //回滚到保存点savepoint的位置
                connection.rollback(savepoint);
                //结束事务
                connection.commit();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5. 关闭资源
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    }
}

批处理

状态通道批处理

package Demo;

import java.sql.*;

public class DEMO5 {

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        Savepoint savepoint =null;


        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="123456";
            String url="jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //手动提交事务
            connection.setAutoCommit(false);
            //3. 定义sql,创建状态通道（进行sql语句的发送）
            statement = connection.createStatement();
            // 4.定义sql
            String sql1 = "insert into teacher(tname) values('张三a')";
            statement.addBatch(sql1);

            String sql2 = "insert into teacher(tname) values('张三b')";
            statement.addBatch(sql2);

            String sql3 = "insert into teacher(tname) values('张三c')";
            statement.addBatch(sql3);
            int[] ints = statement.executeBatch();
            connection.commit();
            for (int anInt : ints) {
                System.out.println("anInt="+anInt);
            }

        } catch (Exception e) {
            e.printStackTrace();
            try {
                //回滚
                connection.rollback();

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }  finally {
            try {
                //5. 关闭资源
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    }
}

预状态通道批量处理

package Demo;

import java.sql.*;

class PreparedStatementBatch {

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement pps = null;
        Savepoint savepoint =null;


        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="123456";
            String url="jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //手动提交事务
            connection.setAutoCommit(false);
            //3. 定义sql,创建状态通道（进行sql语句的发送）
            pps = connection.prepareStatement("insert teacher(tname) values(?)");

            // 4.赋值
            pps.setString(1,"王五A");
            pps.addBatch();

            pps.setString(1,"王五B");
            pps.addBatch();

            pps.setString(1,"王五C");
            pps.addBatch();

            int[] ints = pps.executeBatch();
            connection.commit();
            for (int anInt : ints) {

                System.out.println("anInt="+anInt);
                
            }


        } catch (Exception e) {
            e.printStackTrace();
            try {
                //回滚
                connection.rollback();

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }  finally {
            try {
                //5. 关闭资源
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    }
}

反射处理结果集

//查询全部学生
    public List<Student> getAllStudent(Class cla);
@Override
    public List<Student> getAllStudent(Class cla) {
        Connection connection = null;
        PreparedStatement pps = null;
        ResultSet resultSet = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName = "root";
            String passWord = "123456";
            String url="jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
            connection= DriverManager.getConnection(url,userName,passWord);
            //3.定义sql,创建预状态通道
            String sql = "SELECT * FROM student ";
            pps = connection.prepareStatement(sql);

            //执行sql
            resultSet = pps.executeQuery();
            List students = new ArrayList();

            //1.得到查询结果的列信息
            ResultSetMetaData metaData = resultSet.getMetaData();//存储结果信息
            int columnCount = metaData.getColumnCount();//得到列数
            String[] columnNames = new String[columnCount];
            for (int i = 0; i < columnCount; i++) {
                columnNames[i]=metaData.getColumnName(i+1);//列从1开始算
                System.out.println("getColumnName="+columnNames[i]);
            }
            
            //得到类中所有的方法

                Method[] declaredMethods = cla.getDeclaredMethods();
            try {
                while (resultSet.next()){
                    //1.取出各自的信息
                    Object stu =  cla.newInstance();
                    for (String columnName : columnNames) {
                        String methidName="set"+columnName;
                        for (Method declaredMethod : declaredMethods) {
                            if (declaredMethod.getName().equalsIgnoreCase(methidName)){
                                declaredMethod.invoke(stu,resultSet.getObject(columnName));
                                break;
                            }
                        }
                    }
                    students.add(stu);
                }
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }


            return students;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5.关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return null;
    }

封装工具类

package util;

import java.sql.*;
import java.util.List;

public class DBUtils {
    //定义变量
    private Connection connection;
    private PreparedStatement pps;
    private ResultSet resultSet;
    private int count;//存储受影响的行数

    private String userName="root";
    private String userPass="123456";
    private String url = "jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";

    //2.加载驱动
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //3.获得链接
    protected Connection getConnection(){
        try {
            connection = DriverManager.getConnection(url, userName, userPass);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }

    //4.得到预状态通道
    protected PreparedStatement getPps(String sql){
        try {
            pps = getConnection().prepareStatement(sql);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return pps;
    }
    //5.绑定参数 List保存的是给占位符所赋的值
    protected void param(List list){
        if(list!=null&&list.size()>0){
            for (int i=0;i<list.size();i++){
                try {
                    pps.setObject(i+1,list.get(i));
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    //6.执行操作：增删改和查询
    protected int update(String sql,List list){
        getPps(sql);
        param(list);
        try {
            count=pps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count;
    }

    //7.查询
    protected ResultSet query(String sql,List list){
        getPps(sql);
        param(list);
        try {
            resultSet=pps.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return resultSet;
    }

    //8.关闭资源
    protected void closeAll(){
        try {
            if (connection != null) {
                connection.close();
            }
            if (pps != null) {
                pps.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

 //根据学生id查询学生信息
    public Student getByStuId(int id);
@Override
    public Student getByStuId(int id) {
        Student student = new Student();
        try {
            String sql="select * from student where stuid =?";
            List list = new ArrayList();
            list.add(id);
            ResultSet rs = query(sql, list);
            while (rs.next()){
                student.setStuId(rs.getInt("stuid"));
                student.setStuName(rs.getString("stuname"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }

属性文件

private Connection connection;
    private PreparedStatement pps;
    private ResultSet resultSet;
    private int count;//存储受影响的行数

    private static String userName;
    private static String userPass;
    private static String url;
    private static String dirverName;

    //2.加载驱动
    static {
        try {
            /*InputStream inputStream = DBUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(inputStream);
            dirverName = properties.getProperty("driverclass");
            url = properties.getProperty("url");
            userName = properties.getProperty("uname");
            userPass= properties.getProperty("upass");*/
            ResourceBundle bundle = ResourceBundle.getBundle("db");
            dirverName = bundle.getString("driverclass");
            url = bundle.getString("url");
            userName = bundle.getString("uname");
            userPass = bundle.getString("upass");
            Class.forName(dirverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
创建一个file文件命名为：db.properties
driverclass=com.mysql.cj.jdbc.Driver
uname=root
upass=123456
url=jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC

DBPC

2.加载驱动

3.获得链接

package util;

import org.apache.commons.dbcp.BasicDataSource;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.List;
import java.util.Properties;
import java.util.ResourceBundle;

public class DBUtils {
    //定义变量
    private Connection connection;
    private PreparedStatement pps;
    private ResultSet resultSet;
    private int count;//存储受影响的行数

    private static String userName;
    private static String userPass;
    private static String url;
    private static String dirverName;
    private static BasicDataSource basicDataSource = new BasicDataSource();

    //2.加载驱动
    static {
       /* try {
            *//*InputStream inputStream = DBUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(inputStream);
            dirverName = properties.getProperty("driverclass");
            url = properties.getProperty("url");
            userName = properties.getProperty("uname");
            userPass= properties.getProperty("upass");*//*
            ResourceBundle bundle = ResourceBundle.getBundle("db");
            dirverName = bundle.getString("driverclass");
            url = bundle.getString("url");
            userName = bundle.getString("uname");
            userPass = bundle.getString("upass");
            Class.forName(dirverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }*/
        //DBPC操作
        ResourceBundle bundle = ResourceBundle.getBundle("db");
        dirverName = bundle.getString("driverclass");
        url = bundle.getString("url");
        userName = bundle.getString("uname");
        userPass = bundle.getString("upass");

        basicDataSource.setUsername(userName);
        basicDataSource.setPassword(userPass);
        basicDataSource.setUrl(url);
        basicDataSource.setDriverClassName(dirverName);
        basicDataSource.setInitialSize(10);//设置初始链接数
    }

    //3.获得链接
    protected Connection getConnection(){
        try {
             connection=basicDataSource.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }

    //4.得到预状态通道
    protected PreparedStatement getPps(String sql){
        try {
            pps = getConnection().prepareStatement(sql);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return pps;
    }
    //5.绑定参数 List保存的是给占位符所赋的值
    protected void param(List list){
        if(list!=null&&list.size()>0){
            for (int i=0;i<list.size();i++){
                try {
                    pps.setObject(i+1,list.get(i));
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    //6.执行操作：增删改和查询
    protected int update(String sql,List list){
        getPps(sql);
        param(list);
        try {
            count=pps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count;
    }

    //7.查询
    protected ResultSet query(String sql,List list){
        getPps(sql);
        param(list);
        try {
            resultSet=pps.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return resultSet;
    }

    //8.关闭资源
    protected void closeAll(){
        try {
            if (connection != null) {
                connection.close();
            }
            if (pps != null) {
                pps.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

C3P0

1.创建一个文件c3p0-config.xml

<?xml version="1.0" encoding="utf-8"?>
<c3p0-config>  
    <!-- 默认配置，如果没有指定则使用这个配置 -->
    <default-config>
              <!-- 基本配置 -->
              <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
              <property name="jdbcUrl">jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC</property>
              <property name="user">root</property>
              <property name="password">123456</property>
              <!--扩展配置-->
              <!-- 连接超过30秒报错-->
              <property name="checkoutTimeout">30000</property>
              <!--30秒检查空闲连接 -->
              <property name="idleConnectionTestPeriod">30</property>
              <property name="initialPoolSize">10</property>
              <!-- 30秒不适用丢弃-->
              <property name="maxIdleTime">30</property>
              <property name="maxPoolSize">100</property>
              <property name="minPoolSize">10</property>
              <property name="maxStatements">200</property>
              </default-config>
        </c3p0-config>
//C3P0
    ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
 Connection connection = comboPooledDataSource.getConnection();

德鲁伊

1.添加jar包

ResourceBundle bundle = ResourceBundle.getBundle("db");
        dirverName = bundle.getString("driverclass");
        url = bundle.getString("url");
        userName = bundle.getString("uname");
        userPass = bundle.getString("upass");

        dataSource.setUsername(userName);
        dataSource.setPassword(userPass);
        dataSource.setUrl(url);
        dataSource.setDriverClassName(dirverName);
        dataSource.setInitialSize(8);

大胖驾到
关注
关于调用数据库函数executeUpdate抛出异常
weixin_30414155的博客
06-11 1130
2018.06.11写一个web程序的时候发现了一个问题，解决了好几天都没解决，并且也找不到问题所在。偶然百度找到了根源所在，希望可以帮到大家。 1 在调用这个函数的时候一直抛异常。刚开始我还以为是代码的错误，查了好几天，都没查到。结果是数据库锁的问题 2.1首先看看你的jsp页面提交到servlet是否成功！，在你的servlet的post()方法里System.out.print("...
如何解决Exception in thread “main” java.sql.SQLException: Statement.executeUpdate() or Statement.execute
qq_34589842的博客
01-09 3131
黑马程序员javaweb基础教程 视频jdbc快速入门时出现的报错。
执行SQL语句的方式
GTC_GZ的博客
10-21 735
osc_t0xmr011 2019/08/31 20:16 阅读数 18 程序员必读：双11数据库老崩溃？这里有几招必杀技了解一下！>>> JDBC不仅可执行查询，也可以执行DDL,DML等SQL语句，从而允许通过JDBC最大限度地控制数据库。 使用executeUpdate或者使用executeLargeUpdate方法来执行DDL和DML语句： 编写程序，通过executeUpdate方法在mysql当前数据库下创建一个数据库表 示范： public class JDBC { Stri
executeUpdate()方法报错或者不执行
weixin_45827203的博客
06-20 8309
就是这句话死活不执行下去 因为我使用了查询（select）语句，所以不执行。微软官方文档明确指出， 只能使用SQL INSERT、UPDATE、MERGE 或 DELETE 语句；或是不返回任何内容的 SQL 语句，如 DDL 语句。 官方文档 https://docs.microsoft.com/zh-cn/sql/connect/jdbc/reference/executeupdate-method?view=sql-server-ver15 com.microsoft.sqlserver.jdbc
JDBC使用
weixin_42442523的博客
09-21 181
JDBC
mysql jdbc 驱动 适用于5.6版本及以下数据库
11-09
mysql jdbc 驱动 适用于5.6版本及以下数据库 mysql jdbc 驱动 适用于5.6版本及以下数据库 mysql jdbc 驱动 适用于5.6版本及以下数据库 mysql jdbc 驱动 适用于5.6版本及以下数据库 mysql jdbc 驱动 适用于5.6版本及...
mysql jdbc 驱动 适用于5.7及以上版本数据库
11-09
mysql jdbc 驱动 适用于5.7及以上版本数据库 mysql jdbc 驱动 适用于5.7及以上版本数据库 mysql jdbc 驱动 适用于5.7及以上版本数据库 mysql jdbc 驱动 适用于5.7及以上版本数据库 mysql jdbc 驱动 适用于5.7及以上...
sqljdbc4-4.0.jar
10-09
《SQL Server JDBC驱动4.0版本详解》 在Java编程中，与数据库的交互是必不可少的一部分，而SQL Server作为一款广泛应用的关系型数据库管理系统，其在Java环境下的连接和操作主要依赖于JDBC（Java Database ...
sqljdbc4.jar, sqljdbc41.jar, sqljdbc42.jar三个包
04-09
标题中的"sqljdbc4.jar, sqljdbc41.jar, sqljdbc42.jar"是指Microsoft SQL Server为Java应用程序提供的Java Database Connectivity (JDBC)驱动程序。这些JAR文件是数据库连接的关键，允许Java代码与SQL Server进行...
java连接sql server的问题收集
Karl的专栏
04-16 698
1.提示登陆名无效,只要在sql server下的企业管理器里在要连接的数据库下增加一个用户,设置登入名和密码.2.java里的sql代码:insert SoduDateBase.dbo.Questio_Level1  values("+sq+""+ ")" 注意变量要用单引号.3.ResultSet的first不能用的原因: Statement   stmt   =   con.create
statement.executeQuery(sql)出错的一种排查方法
pinklatte的博客
05-28 1万+
在IDE里调试程序总是在执行SQL语句时报错（ps:如何定位错误？）,这是一件很头疼的事。 一种比较有效的方法是： （1）调用printf等输出函数，得到SQL语句 eg: String sql = "select * from suser where name='"+name+"' and password='"+psd+"';"; System.out.println(sql);//...
PreparedStatement 的 execute方法和executeUpdate方法区别
热门推荐
拼老命的程序猿
04-12 3万+
方法executeUpdate    用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQL DDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE 语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数（int），指示受影响的行数（即更新计数）。对于 CR
statement接口中executeUpdate()、executeQuery()、execute()方法处理
djun100的专栏
07-08 1436
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; public class ...
执行executeUpdate()；等方法误传入了sql参数导致运行出错的细节问题
扬帆天涯
05-25 5707
问题源头 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘?,?,?,?)’ at line 1 您的SQL语法有错误;检查与MySQL服务器版本对应的手册，在第1行“?，?，?...
MySQL中几种常见的行格式
最新发布
weixin_45923633的博客
09-13 393
动态格式会根据行中数据的实际大小动态地调整行的存储方式。如果行中的某个字段是变长的，那么这个字段的实际数据将被存储在行的末尾，而不会在行的开头占用固定的空间。这种格式适用于所有列都是固定长度的情况，可以提供最快的访问速度，但可能会浪费存储空间。：紧凑格式试图减少内部碎片，使得行的平均大小变小，从而在一个数据页中可以存储更多的行。：页面格式是一种较新的行存储格式，它旨在提高大表的访问性能和存储效率。是用来指定表中行的存储格式的属性。不同的行格式会影响数据的存储方式，进而影响存储空间的使用效率和访问性能。
【MySQL】表的操作【有关表结构的操作】【创建、查看、删除、修改表结构】
aaa114514aaaa的博客
09-12 1161
对于mysql数据库的表的结构的查看，创建、删除、修改、【对字段的添加、修改】
JDBC入门与WebLogicJDBC实践
"该资源主要涉及JDBC的使用和解析，包括通过WebLogicJDBC进行数据库连接、查询，以及理解JDBC的工作原理和事务管理。此外，还提供了一个使用JDBC连接和查询数据库的示例代码，分析了其执行步骤。" 在Java世界中，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值