SQLServer工具类 – 安然的尾巴

内容隐藏

1.3.2. jdbc.properties文件

1.4. class.forname加载驱动

1.5. 加载驱动

1.6. 通过 DriverManager 获取数据库连接

1.7. 反射

1.8. 执行

使用工具类前请导入sqljdbc42.jarsqljdbc

import java.io.FileInputStream;
import java.io.IOException;
import java.io.StringWriter;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class JDBCUtils {

    private static String url;

    private static String user;

    private static String pwd;

    static {
        try {
            Properties properties = new Properties();

            //加载配置文件

            properties.load(new FileInputStream("jdbc.properties"));

            //读取配置文件
            url = properties.getProperty("url");

            user = properties.getProperty("user");

            pwd = properties.getProperty("pwd");

            //加载驱动
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException | IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() {
        Connection connection =null;
        try {
             connection = DriverManager.getConnection(url,user,pwd);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return connection;
    }
    // 关闭连接之后  statment也会关闭  但是是要等系统回收之后才关闭 比较浪费资源
    public static  void  close(Connection connection) {

        try {
            connection.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    //关闭连接
    public static void close(Statement statement,Connection connection) {

        try {
            try {
                if (statement!=null) {
                    statement.close();
                }
            } finally {
                if (connection!=null) {
                    connection.close();
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void close(ResultSet rs) {
        Connection connection =null;
        Statement statement =null;

        try {
            try {
                if (rs!=null) {
                    statement = rs.getStatement();
                    rs.close();
                }
            }finally {
                if (statement!=null) {
                    connection = statement.getConnection();
                    statement.close();

                }
                if (connection!=null) {
                    connection.close();
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public static void close(ResultSet rs,Statement statement,Connection connection) {

        try {
            try {
                if (rs!=null) {
                    rs.close();
                }
            } finally {
                if (statement!=null) {
                    statement.close();
                }if (connection!=null) {
                    connection.close();
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    //通用的增删 改操作
    public static int update(String sql,Object...args) {

        //影响的行数
        int count =0;
        Connection connection =null;
        PreparedStatement preparedStatement=null;
        try {
            try {
                //获取连接
                 connection =getConnection();

                //创建执行对象
                 preparedStatement =connection.prepareStatement(sql);
                //设置参数
                 if (args!=null) {
                     for(int i=1;i<=args.length;i++) {
                            preparedStatement.setObject(i, args[i-1]);
                        }
                }

                //执行
                count = preparedStatement.executeUpdate();
            } finally {
                if (preparedStatement!=null) {
                    preparedStatement.close();
                }
                if (connection!=null) {
                    connection.close();
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return count;
    }

    //查询注意 记得关闭到ResultSet

    public static ResultSet queryResultSet(String sql,Object...agrs) {

        ResultSet rs =null;
        try {
            Connection  connection = getConnection();

            //创建预编译语句
            PreparedStatement preparedStatement =connection.prepareStatement(sql);
            //添加参数
            if (agrs!=null) {
                 for(int i=1;i<=agrs.length;i++) {
                        preparedStatement.setObject(i, agrs[i-1]);
                    }
            }

             rs = preparedStatement.executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return rs;

    }

    //通用的查询 返回 List<实体类> 集合

    public static <T> List<T> queryList(Class<T> class1,String sql,Object...args) {
        List<T> list = new ArrayList<T>();
        Connection connection =null;
        PreparedStatement preparedStatement =null;

        ResultSet rs =null;

        try {
            connection = getConnection();

            preparedStatement = connection.prepareStatement(sql);

            //设置参数
            if (args!=null) {
                for (int i = 1; i <= args.length; i++) {
                    preparedStatement.setObject(i, args[i-1]);
                }
            }
            //查询
            rs =  preparedStatement.executeQuery();
            //获取元数据
            ResultSetMetaData metaData =  rs.getMetaData();
            //拿到有多少列
            int count = metaData.getColumnCount();          

            while(rs.next()) {              
                T t = class1.newInstance();             
                for (int i = 1; i <=count; i++) {
                    //getColumnLabel(i) 拿到别名  没有别名就是列名
                    Field field = class1.getDeclaredField(metaData.getColumnLabel(i));
                    field.setAccessible(true);
                    field.set(t, rs.getObject(i));

                }
                list.add(t);                
            }
        } catch (InstantiationException | IllegalAccessException | NoSuchFieldException | SecurityException
                | IllegalArgumentException | SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            close(rs,preparedStatement,connection);
        }       
        return list;        
    }
}

极度依赖工具类

//sql语句 args参数(可加可不加)
public static int update(String sql,Object...args) {

    //影响的行数
    int count =0;
    Connection connection =null;
    PreparedStatement preparedStatement=null;
    try {
        try {
            //获取连接
            connection =getConnection();

            //创建执行对象
            preparedStatement =connection.prepareStatement(sql);
            //设置参数
            if (args!=null) {
                for(int i=1;i<=args.length;i++) {
                    preparedStatement.setObject(i, args[i-1]);
                }
            }

            //执行
            count = preparedStatement.executeUpdate();
        } finally {
            if (preparedStatement!=null) {
                preparedStatement.close();
            }
            if (connection!=null) {
                connection.close();
            }
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    return count;
}

//queryList(sql语句 args参数(可加可不加))
int count =JDBCUtils.update(sql, 3);

//sql语句 args参数(可加可不加)
public static <T> List<T> queryList(Class<T> class1,String sql,Object...args) {
    List<T> list = new ArrayList<T>();
    Connection connection =null;
    PreparedStatement preparedStatement =null;

    ResultSet rs =null;

    try {
        connection = getConnection();

        preparedStatement = connection.prepareStatement(sql);

        //设置参数
        if (args!=null) {
            for (int i = 1; i <= args.length; i++) {
                preparedStatement.setObject(i, args[i-1]);
            }
        }
        //查询
        rs =  preparedStatement.executeQuery();
        //获取元数据
        ResultSetMetaData metaData =  rs.getMetaData();
        //拿到有多少列
        int count = metaData.getColumnCount();

        while(rs.next()) {

            T t = class1.newInstance();

            for (int i = 1; i <=count; i++) {
                //getColumnLabel(i) 拿到别名  没有别名就是列名
                Field field = class1.getDeclaredField(metaData.getColumnLabel(i));
                field.setAccessible(true);
                field.set(t, rs.getObject(i));

            }
            list.add(t);

        }
    } catch (InstantiationException | IllegalAccessException | NoSuchFieldException | SecurityException
             | IllegalArgumentException | SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally {
        close(rs,preparedStatement,connection);
    }

    return list;

}

//如果字段名和数据库的字段名不一样  那你就给查询的时候添加一样的别名
//数据库终端 别名是实体类字段
//queryList(sql语句 args参数(可加可不加))
List<Student> list = JDBCUtils.queryList(Student.class,"select* from stuInfo",参数(可加可不加));

//查询注意 记得关闭到ResultSet   
public static ResultSet queryResultSet(String sql,Object...agrs) {      
    ResultSet rs =null;
    try {
        Connection  connection = getConnection();           
        //创建预编译语句
        PreparedStatement preparedStatement =connection.prepareStatement(sql);
        //添加参数
        if (agrs!=null) {
            for(int i=1;i<=agrs.length;i++) {
                preparedStatement.setObject(i, agrs[i-1]);
            }
        }           
        rs = preparedStatement.executeQuery();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }       
    return rs;      
}

String sql = "select * from stuInfo";
//
List<User> users = new ArrayList<User>();
ResultSet rs = JDBCUtils.queryResultSet(sql);
try {
    while(rs.next()) {        
        User user = new User();
        //将数据集合读取
        user.name = rs.getObject(1);
        user.remark = rs.getObject(2);
        users.add(user);
    }
} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}finally {
    JDBCUtils.close(rs);
}

部分依赖工具类

//获取连接
Connection connection = JDBCUtils.getConnection();

//      String sql = "delete from Car where cid=?";
//      String sql = "insert into Car values(?,?,?,?,?,?)";
//      String sql = "delete from Car where cid=?";
String sql = "update  Car set carName =? where cid=?";
PreparedStatement pStatement  =null;
try {
    //创建执行对象 创建预编译语句
    pStatement = connection.prepareStatement(sql);

    //设置参数

    pStatement.setObject(1, "宝马");
    pStatement.setObject(2, 2);

    //          pStatement.setObject(1, "特斯拉");
    //          pStatement.setObject(2, "小车");
    //          pStatement.setObject(3, null);
    //          pStatement.setObject(4, null);
    //          pStatement.setObject(5, null);
    //          pStatement.setObject(6, null);
    //          pStatement.execute();

    int count = pStatement.executeUpdate();

    System.out.println(count);
} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}finally {
    JDBCUtils.close(pStatement,connection);
}

class User{
    public String name;
    public String remark;
}

工具类解读

代码块

代码化块，类似于方法，将语句封装在方法体中
但和方法不同，没有方法名，没有返回，没有参数，只有方法体，而且不用通过对象或类显式调用，而是加载类时，或创建对象时隐式调用。

静态代码块

调用指定类的静态方法块，只会加载一次
给定的类如果之前没有被初始化过，那么会被初始化。

Properties 类

获取文件中的属性

//创建一个Properties对象
Properties properties = new Properties();
try {
//从输入流中读取属性列表（键和元素对）。
    properties.load(new FileInputStream("jdbc.properties"));
} catch (IOException e) {
    e.printStackTrace();
}
//用指定的键在此属性列表中搜索属性。
String url = properties.getProperty("url");
String user = properties.getProperty("user");

jdbc.properties文件

//键=元素
url=jdbc:sqlserver://k2.getmc.cn:15392;database=test
user=sa
pwd=ll123456

class.forname加载驱动

调用指定类的静态方法块（调用驱动的静态方法块）
给定的类如果之前没有被初始化过，那么会被初始化。

加载驱动

try {
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (ClassNotFoundException e) {
    e.printStackTrace();
}

通过 DriverManager 获取数据库连接

Connection  connection =null;
try {
    connection = DriverManager.getConnection(url,user,pwd);
} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

反射

//getColumnLabel(i) 拿到别名  没有别名就是列名
//getField拿到公共属性
//getDeclaredField拿到私有属性
Field field = class1.getDeclaredField(metaData.getColumnLabel(i));
//设置属性为可访问
field.setAccessible(true);
//键,元素
field.set(t, rs.getObject(i));

执行

//影响的行数
int count =0;
Connection connection =null;
PreparedStatement preparedStatement=null;
try {
    try {
        //获取连接
        connection =getConnection();

        //创建执行对象
        preparedStatement =connection.prepareStatement(sql);
        //设置参数
        if (args!=null) {
            for(int i=1;i<=args.length;i++) {
                preparedStatement.setObject(i, args[i-1]);
            }
        }
        //执行
        count = preparedStatement.executeUpdate();
    } finally {
        if (preparedStatement!=null) {
            preparedStatement.close();
        }
        if (connection!=null) {
            connection.close();
        }
    }
} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}