JDBC Connection Database

Source: Internet
Author: User
Tags readline stmt stringbuffer

  1. ? Create a program that connects to the database in JDBC with 7 steps:
  2. 1. Load the JDBC driver:
  3. Before connecting to the database, first load the driver of the database you want to connect to the JVM (Java Virtual machine),
  4. This is achieved through the static method forname (String className) of the Java.lang.Class class.
  5. For example:
  6. static{
    try {
    Publish using
    Context context = new InitialContext (); Create an up and down file object
    DataSource = (DataSource) context.lookup ("Java:comp/env/jdbc/jspjndi");
    Test using in development
    Properties Props = new properties ();
    Props.load (DbHelper.class.getClassLoader (). getResourceAsStream ("db.properties"));
    DataSource = Basicdatasourcefactory.createdatasource (props);
    } catch (Exception e) {
    E.printstacktrace ();
    }
    }

  7. After a successful load, an instance of the driver class is registered in the DriverManager class.
  8. 2. Provide the URL of the JDBC connection
  9. ? The connection URL defines the protocol, sub-protocol, and data source identity when the database is connected.
  10. Writing form: Protocol: Sub-Protocol: Data source identification
  11. Protocol: Always start with JDBC in JDBC
  12. Sub-Protocol: A bridge-connected driver or database management system name.
  13. Data source identification: The tag locates the address of the database source and the connection port.
  14. For example: (MySQL connection URL)
  15. Jdbc:mysql:
  16. LOCALHOST:3306/TEST?USEUNICODE=TRUE&CHARACTERENCODING=GBK;    
  17. Useunicode=true: Indicates the use of the Unicode character set. If Characterencoding is set to
  18. gb2312 or GBK, this parameter must be set to true . CHARACTERENCODING=GBK: The character encoding method.
  19. 3. Create a connection to the database
  20. To connect to a database, you need to request and obtain a connection object from Java.sql.DriverManager.
  21. The object represents a connection to a database.
  22. Get database Connection object
    public static Connection Getconn () {
    Connection con = null;
    if (DataSource! = null) {
    try {
    con = datasource.getconnection (); Fetch a connection in the data source
    } catch (SQLException e) {
    E.printstacktrace ();
    }
    }
    return con;
    }

  23. 4 Closing the Connection object
  24. Public static void CloseAll (ResultSet rs,statement stmt,connection conn) {
    //Close result set object
    if (null!=rs) {
    try {
    rs.close ();
    } catch (SQLException e) {
    e.printstacktrace ();
    }
    }
    //Close Statement Object
    if (null!=stmt) {
    try {
    stmt.close ();
    } catch (SQLException e) {
    //TODO auto-generated catch block
    e.printstacktrace ();
    }
    }
    //Close Database Connection object
    if (null!=conn) {
    try {
    conn.close ();
    } catch (SQLException e) {
    //TODO auto-generated catch block
    e.printstacktrace ();
    }
    }
    }

    Public static void CloseAll (ResultSet rs,preparedstatement pstmt,connection conn) {
    //Close result set object
    if (null!=rs) {
    try {
    rs.close ();
    } catch (SQLException e) {
    e.printstacktrace ();
    }
    }
    //Close Statement Object
    if (null!=pstmt) {
    try {
    pstmt.close ();
    } catch (SQLException e) {
    //TODO auto-generated catch block
    e.printstacktrace ();
    }
    }
    //Close Database Connection object
    if (null!=conn) {
    try {
    conn.close ();
    } catch (SQLException e) {
    //TODO auto-generated catch block
    e.printstacktrace ();
    }
    }
    }

  25. 5, the method of setting parameters
  26. /**

  27. * @param pstmt Precompiled objects

  28. * @param the set of values passed by the params

  29. * @throws SQLException
    */
    private static void SetParams (PreparedStatement pstmt,object...params) throws sqlexception{
    if (null!=params&&params.length>0) {
    for (int i=0;i<params.length;i++) {
    if (Params[i] instanceof Date) {
    The params stores the values in the same order as the?
    Pstmt.settimestamp (i+1, New Timestamp ((Date) params[i]). GetTime ());
    }else{
    Pstmt.setobject (i+1, params[i])//params store values in the same order as?
    }

    }
    }
    }

  30. 6. Increase and revise operation
  31. /**
    * Adding and deleting changes
    * @param SQL
    * @param params
    * @return
    * @throws SQLException
    */
    public static int doupdate (String sql,object...params) {
    Connection Conn =null;
    preparedstatement pstmt =null;
    int result =-1;

    try {
    conn=getconn ();
    pstmt=conn.preparestatement (SQL);
    SetParams (pstmt, params);
    result =pstmt.executeupdate ();
    } catch (SQLException e) {
    //TODO auto-generated catch block
    e.printstacktrace ();
    }finally {
    CloseAll (NULL, PSTMT, conn);
    }
    return result;
    }

    public static int doupdate (list<string> sqls, object[]...params) throws sqlexception{
    Connection Conn =null;
    preparedstatement pstmt =null;
    int result =-1;
    try {
    conn = Getconn ();
    Conn.setautocommit (false);
    if (null!= sqls && sqls.size () >0) {
    //Circular SQL statements
    for (int i=0;i<sqls.size (); i++) {
    String Sql=sqls.get (i);
    pstmt =conn.preparestatement (SQL);
    //set for current SQL statement
    SetParams (pstmt, params[i]);
    result +=pstmt.executeupdate ();
    }
    }
    //Submit things
    conn.commit ();
    } catch (Exception e) {
    e.printstacktrace ();
    //Roll back things
    result=0;
    Conn.rollback ();
    } finally {
    //Reply to the scene
    Conn.setautocommit (true);
    CloseAll (NULL, PSTMT, conn);
    }
    return result;
    }

    public static int updateimg (String sql,int ID, file file) throws filenotfoundexception{
    FileInputStream in = new FileInputStream (file);
    Connection Conn =null;
    preparedstatement pstmt =null;
    int result = 0;
    try {
    pstmt = conn.preparestatement (sql);
    Pstmt.setbinarystream (1, in, (int) file.length ());
    Pstmt.setint (2, id);
    result = Pstmt.executeupdate ();
    } catch (SQLException e) {

    } finally {
    CloseAll (NULL, PSTMT, conn);
    }

    return result;

  32. 6. Query operation

  33. /**
    * Query multiple records
    * @param SQL
    * @param params
    * @return
    * @throws SQLException
    */
    Public static list<map<string, object>> Findmultiobject (String sql,object...params) throws sqlexception{
    list<map<string, object>> List =new arraylist<map<string,object>> ();
    Connection Conn =null;
    preparedstatement pstmt =null;
    ResultSet rs =null;
    map<string, object> Map =null;
    try {
    Conn =getconn ();
    pstmt =conn.preparestatement (SQL);
    SetParams (pstmt, params);
    rs =pstmt.executequery ();
    list<string > ColumnNames =getallcolumnnames (RS);//Get all the lists in the results
    While (Rs.next ()) {
    map = new hashmap<string,object> ();
    For (String cn:columnnames) {//Loop column name, the list action Map key, based on the list gets the value of each column
    Map.put (CN, Rs.getobject (CN));
    }
    list.add (map);
    }
    } finally {
    CloseAll (RS, PSTMT, conn);
    }

    return list;
    }

    /**
    * Query Single record select * from table name where id = 1
    * @param SQL
    * @param params
    * @return
    * @throws SQLException
    */
    Public static map<string,object> findsingleobject (String sql, object...params) throws sqlexception{
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    map<string,object> Map = null;
    try {
    conn = Getconn ();
    pstmt = conn.preparestatement (sql);
    SetParams (pstmt, params);
    rs = Pstmt.executequery ();
    list<string> ColumnNames = Getallcolumnnames (RS);//Get all lists in the result set
    While (Rs.next ()) {
    map = new hashmap<string,object> ();
    For (String cn:columnnames) {//Loop column name, the list action Map key, based on the list gets the value of each column
    Map.put (CN, Rs.getobject (CN));
    }
    }
    }finally{
    CloseAll (RS, PSTMT, conn);
    }
    return map;
    }

  34. 8. Operation result Operation
    /**
    * Get all column names based on the result set object there is a list of jdbc2.0 metadata in the collection
    * @param RS
    * @return
    * @throws SQLException
    */
    private static list<string> Getallcolumnnames (ResultSet rs) throws sqlexception{
    list<string> columnnames =new arraylist<string> ();
    if (null!=rs) {
    for (int i=0; I<rs.getmetadata (). getColumnCount (); i++) {
    Columnnames.add (Rs.getmetadata (). getColumnName (i+1));
    }
    }
    return columnnames;
    }

    Public static<t> T Get (class<t> clazz, String sql, object...params) throws SQLException {
    Return Mapping2obj (Clazz, Findsingleobject (SQL, params));
    }

    Public static <T> list<t> List (class<t> clazz, String sql, object...params) throws SQLException {
    list<map<string, object>> results = findmultiobject (sql, params);
    list<t> ts = null;
    if (results! = null) {
    ts = new arraylist<t> ();
    for (map<string, object> result:results) {
    Ts.add (Mapping2obj (clazz, result));
    }
    }
    return TS;
    }

    private static <T> T mapping2obj (class<t> clazz, map<string,object> temps) {
    T t = null;
    try {
    t = clazz.newinstance ();
    } catch (Instantiationexception | Illegalaccessexception e) {
    e.printstacktrace ();
    }
    method[] ms = Clazz.getdeclaredmethods ();//Take All methods
    For (Method m:ms) {
    String mn = M.getname ();//Fetch to Method name
    if (Mn.startswith ("set")) {
    String pt = m.getparametertypes () [0].getname ();///parameter type taken to the Set method
    Object obj = Temps.get (Mn.replace ("Set", ""). toUpperCase ()); Takes the value of the set method corresponding to the database field
    try {
    if (obj! = null) {
    if (pt.endswith ("int") | | | pt.endswith ("Integer")) {
    M.invoke (t, ((BigDecimal) obj). Intvalue ());
    } else if (Pt.endswith ("double") | | | pt.endswith ("double")) {
    M.invoke (t, ((BigDecimal) obj). Doublevalue ());
    } else if (Pt.endswith ("Java.util.Date")) {
    M.invoke (t, (Timestamp) obj);//11g
    //m.invoke (T, New Date ((java.sql.Date) obj). GetTime ());//10g
    } else {
    if (Obj.getclass (). GetName (). EndsWith ("CLOB")) {
    obj = clobtostring ((CLOB) obj);
    }
    M.invoke (t, obj);
    }
    }
    } catch (Exception e) {
    e.printstacktrace ();
    }
    }
    }
    return t;
    }

    Public static String clobtostring (Clob Clob) {
    String restring = "";
    Reader is = null;
    try {
    is = Clob.getcharacterstream ();
    } catch (SQLException e) {
    e.printstacktrace ();
    }
    //Get stream
    bufferedreader br = new BufferedReader (is);
    String s = null;
    try {
    s = br.readline ();
    } catch (IOException e) {
    e.printstacktrace ();
    }
    stringbuffer sb = new StringBuffer ();
    While (s! = null) {
    //Execution loop takes all the strings out and pays the value to StringBuffer from StringBuffer to string
    Sb.append (s);
    try {
    s = br.readline ();
    } catch (IOException e) {
    e.printstacktrace ();
    }
    }
    restring = sb.tostring ();
    return restring;
    }
    }

JDBC Connection Database

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.