JDBC使用

不同数据库的forName、url书写方式不同

首先应下载相关驱动并导入

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class sqlserver1 {
    public static void main(String[] args) throws Exception{

        //1.注册驱动
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        //2.获取连接
        String url="jdbc:sqlserver://localhost:1433;DatebaseName=LJY";
        String username="sa";
        String passward="123456";
        Connection conn= DriverManager.getConnection(url,username,passward);
        System.out.println("连接成功");

        //3.定义sql
        String sql="use LJY update authors set state='元旦' where au_id=123";

        //4.获取sql执行的对象statement
        Statement stmt=conn.createStatement();

        //5.执行sql
        int count=stmt.executeUpdate(sql);
        System.out.println(count);

        //6.释放资源
        stmt.close();
        conn.close();
    }
}

在定义sql语句时，若不添加：use 数据库名

可能出现:对象名无效

Connection使用：

1.获取执行的sql对象

·prepareStatement：

        在url处添加预编译：useServerPrepStmts=true

        setXXX(参数1，参数2)：设置？的值，参数一是？位置，参数二是赋予的值

        executeUpdate()/executeuery():执行SQL，无需传递sql

import java.sql.*;

public class sqlserver1 {
    public static void main(String[] args) throws Exception {

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        String url = "jdbc:sqlserver://localhost:1433;DatebaseName=LJY";
        String username = "sa";
        String passward = "123465";
        Connection conn = DriverManager.getConnection(url, username, passward);
        System.out.println("连接成功");

        String uname="lisisi";
        String pwd="456faga";

        //防止字符串拼接,将敏感字符转义
        String sql = "use LJY select * from cjb where uname=? and pwd=?";

        PreparedStatement stmt = conn.prepareStatement(sql);

        //设置？的值
        stmt.setString(1,uname);
        stmt.setString(2,pwd);

        //执行sql
        ResultSet count = stmt.executeQuery();
        
        if(count.next()) {
            System.out.println("-------成功--------");
        }else{
            System.out.println("失败");
        }
        stmt.close();
        conn.close();
    }
}

2.事务管理

        当代码断中出现异常时，数据库中的数据不会更改，事务回滚到数据的原始状态

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class sqlserver1 {
    public static void main(String[] args) throws Exception{

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        String url="jdbc:sqlserver://localhost:1433;DatebaseName=LJY";
        String username="sa";
        String passward="123456";
        Connection conn= DriverManager.getConnection(url,username,passward);
        System.out.println("连接成功");

        String sql1="use LJY update authors set state='湖北' where au_id=123";
        String sql2="use LJY update authors set state='江西' where au_id=124";

        Statement stmt = conn.createStatement();

        try {
            //开启事务
            conn.setAutoCommit(false);

            int count1 = stmt.executeUpdate(sql1);
            System.out.println(count1);

            int count2 = stmt.executeUpdate(sql2);
            System.out.println(count2);

            //提交事务
            conn.commit();
        }catch(Exception throwables){
            //回滚事务
            conn.rollback();
            throwables.printStackTrace();
        }
        stmt.close();
        conn.close();
    }
}

Statement使用：

1.执行SQL语句

获取ResultSet的结果：

        boolean next(): 判断当前行是否有数据，将光标移动到下一行

        xxx getXXX(参数):获取数据

                        ·int getInt(1):获取第一列数据

                        ·String getString("id"):获取列名为id的数据

例：数据库中存在如下数据

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class sqlserver1 {
    public static void main(String[] args) throws Exception {

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        String url = "jdbc:sqlserver://localhost:1433;DatebaseName=LJY";
        String username = "sa";
        String passward = "123456";
        Connection conn = DriverManager.getConnection(url, username, passward);
        System.out.println("连接成功");

        String sql = "use LJY select * from cjb";

        Statement stmt = conn.createStatement();

        //执行sql
        ResultSet count = stmt.executeQuery(sql);

        //当存在数据时光标移动
        while (count.next()) {
            String xh = count.getString(1);
            String kch = count.getString(2);
            int cj = count.getInt("cj");

            System.out.println(xh);
            System.out.println(kch);
            System.out.println(cj);
            System.out.println("------------------");
        }
        stmt.close();
        conn.close();
    }
}

数据库连接池：Druid

public class sqlserver1 {
    public static void main(String[] args) throws Exception {
        //加载配置文件
        Properties prop=new Properties();
        prop.load(new FileInputStream("配置文件路径"));
        //获取连接池对象
        DateSource dateSource=DruidDataSourceFactory.createDataSource(prop);
        //获取数据库连接
        Connection connection=dateSource.getConnection();

        //System.out.println(System.getProperty("user.dir"));配置路径

    }
}