Applet 中访问 sqlite

系统环境：ubuntu 10.04+openjdk-6

关于java如何访问 sqlite3 请查这里

CollectionInfo.java

import java.applet.Applet;
import java.awt.BorderLayout;
import java.awt.Dimension;
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.Vector;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

public class CollectionInfo extends Applet implements ActionListener
{
    JButton recvBt = new JButton("实时接收");
    JButton pauseBt = new JButton("暂停接收");
    JButton searchIdBt = new JButton("查询ID");
    JButton searchAllBt = new JButton("查询所有");
    JButton deleteIdBt = new JButton("删除ID");
    JButton deleteAllBt = new JButton("删除所有");
    JTable dataTable = new JTable();
    JScrollPane sp  = new JScrollPane(dataTable);
    DefaultTableModel dtm = null;

    JPanel p1 = new JPanel();
    JPanel p2 = new JPanel();
    JPanel p3 = new JPanel();
    JPanel p4 = new JPanel();

    String idNum = new String("test");

    DataBase_OP d_op = null;

    Thread tsThread=null;

//    public CollectionInfo()
    public void init()
    {
        setComponentSize();

        setLayout();

        addData("2012","1234","2012-04-06 12:30:45");
        regMouseEvent();
        this.setVisible(true);
    }

    public void setComponentSize()
    {
        Dimension d = new Dimension(100,60);
        recvBt.setPreferredSize(d);
        pauseBt.setPreferredSize(d);
        searchIdBt.setPreferredSize(d);
        searchAllBt.setPreferredSize(d);
        deleteIdBt.setPreferredSize(d);
        deleteAllBt.setPreferredSize(d);

        Dimension d1 = new Dimension(380,400);
        this.dataTable.setPreferredSize(d1);

        Dimension d2 = new Dimension(400,400);
        this.sp.setPreferredSize(d2);
//        recvBt.setBackground(Color.blue);

//        int rowCount = 10;
//        dtm.setNumRows(rowCount);
    }

    public void setLayout()
    {
        p1.setLayout(new BorderLayout(10,10));
        p1.add(recvBt,BorderLayout.NORTH);
        p1.add(pauseBt,BorderLayout.CENTER);
        p1.add(searchIdBt,BorderLayout.SOUTH);

        p2.setLayout(new BorderLayout(10,10));
        p2.add(searchAllBt,BorderLayout.NORTH);
        p2.add(deleteIdBt,BorderLayout.CENTER);
        p2.add(deleteAllBt,BorderLayout.SOUTH);

        p3.setLayout(new BorderLayout(10,10));
        p3.add(p1,BorderLayout.NORTH);
        p3.add(p2,BorderLayout.CENTER);

        setTable();

        p4.setLayout(new FlowLayout(FlowLayout.CENTER,10,30));
        p4.add(p3);

        p4.add(sp);

        this.setLayout(new FlowLayout(FlowLayout.CENTER,10,10));
        add(p4);
    }


    public void regMouseEvent()
    {
        this.recvBt.addActionListener(this);
        this.pauseBt.addActionListener(this);
        this.searchIdBt.addActionListener(this);
        this.searchAllBt.addActionListener(this);
        this.deleteIdBt.addActionListener(this);
        this.deleteAllBt.addActionListener(this);
    }

    public void actionPerformed(ActionEvent e)
    {
        String args = e.getActionCommand();
        if (args == "实时接收")
        {
            this.runtimeDisplay();
        }
        else if (args == "暂停接收")
        {
            this.pauseDisplay();
        }
        else if (args == "查询ID")
        {
            this.searchById();
        }
        else if (args == "查询所有")
        {
            this.searchAll();
        }
        else if (args == "删除ID")
        {
            this.deleteById();
        }
        else if (args == "删除所有")
        {
            this.deleteAll();
        }
    }

    public void runtimeDisplay()
    {
        System.out.println("hello,it's runtime clicked\n");
    }

    public void pauseDisplay()
    {
        System.out.println("hello,it's pause clicked\n");
    }

    public void searchById()
    {
        System.out.println("hello,it's searchById clicked\n");

//        inputDialog myDialog = new inputDialog();
//        this.idNum = myDialog.getValue();
//        System.out.println("CollectionInfo: " + idNum);

        String s1 = JOptionPane.showInputDialog(null,"请输入ID:","输入对话框",JOptionPane.PLAIN_MESSAGE);
        this.clearTable();
        d_op = new DataBase_OP(this);
        d_op.searchById(s1);
    }

    public void searchAll()
    {
        System.out.println("hello,it's searchAll clicked\n");
        this.clearTable();

        d_op = new DataBase_OP(this);
        d_op.searchAll();
    }

    public void deleteById()
    {
        System.out.println("hello,it's deleteById clicked\n");
        this.clearTable();
    }

    public void deleteAll()
    {
        System.out.println("hello,it's deleteAll clicked\n");
        this.clearTable();
    }


    public void setTable()
    {
//        Vector vCdata = new Vector();
//        vCdata.add("ID");
//        vCdata.add("数据");
//        vCdata.add("时间");
//
//        dataTable.setModel(dtm);
//
//        for (int i = 0;i < vCdata.size();i++)
//            dtm.addColumn((String)vCdata.elementAt(i));

        Object head[] = {"ID","数据","时间"};
        dtm = new DefaultTableModel(head,0);
        dataTable.setModel(dtm);

        dataTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
        dataTable.getColumn(head[0]).setPreferredWidth(100);
        dataTable.getColumn(head[1]).setPreferredWidth(100);
        dataTable.getColumn(head[2]).setPreferredWidth(180);
    }

    public void addData(String id,String data,String time)
    {
        Vector<String> vRdata = new Vector<String>();
        vRdata.add(id);
        vRdata.add(data);
        vRdata.add(time);

        dtm.addRow(vRdata);

//        dtm.setValueAt(vRdata, 0, 0);
    }

    public void clearTable()
    {
        int i = this.dtm.getRowCount();
        for (;i > 0;i--)
        {
            System.out.println(i);
            this.dtm.removeRow(i-1);
        }
    }

//    public static  void main(String args[])
    public void start()
    {
        CollectionInfo ci = new CollectionInfo();
        ci.setSize(600, 500);
//        ci.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    }

}

DataBase_OP.java

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

public class DataBase_OP  implements Runnable
{
    Thread tsThread=null;
    CollectionInfo tmpInfo;

    public DataBase_OP(CollectionInfo myInfo)
    {
        System.out.println("it's database_op");
        tmpInfo = myInfo;
    }

    public Connection getConnection()
    {
        try
        {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:data.db");
            return conn;
        }
        catch(Exception e)
        {
            System.out.println("123exception");
            e.printStackTrace();
            return null;
        }

    }

    public void searchAll()
    {
        try
        {
            Connection conn = getConnection();
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("select * from save_data;");
            int i = 0;
            while (rs.next())
            {
                i++;
                System.out.print("ID:" +i);
                System.out.print("num: " + rs.getString("num"));
                System.out.print("id: " + rs.getString("id"));
                System.out.print("data: " + rs.getString("data"));
                System.out.println("time: " + rs.getString("time"));
                tmpInfo.addData(rs.getString("id"), rs.getString("data"), rs.getString("time"));
            }

            rs.close();
            stat.close();
            conn.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    public void searchById(String id)
    {
        try
        {
            Connection conn = getConnection();
            PreparedStatement stat = conn.prepareStatement("select * from save_data where id=?;");
            stat.setString(1, id);
            ResultSet rs = stat.executeQuery();
            int i = 0;
            while (rs.next())
            {
                i++;
                System.out.print("ID:" +i);
                System.out.print("num: " + rs.getString("num"));
                System.out.print("id: " + rs.getString("id"));
                System.out.print("data: " + rs.getString("data"));
                System.out.println("time: " + rs.getString("time"));
                tmpInfo.addData(rs.getString("id"), rs.getString("data"), rs.getString("time"));
            }

            rs.close();
            stat.close();
            conn.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }


    public void deleteAll()
    {
        try
        {
            Connection conn = getConnection();
            Statement stat = conn.createStatement();
            stat.executeUpdate("delete from save_data;");

            stat.close();
            conn.close();

            this.searchAll();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    public void deleteById(String id)
    {
        try
        {

            Connection conn = getConnection();
            PreparedStatement stat = conn.prepareStatement("delete from save_data where id=?;");
            stat.setString(1, id);
            stat.executeUpdate();

            stat.close();
            conn.close();

            this.searchAll();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    public void insert(String id,String data,String time)
    {
        try
        {
            Connection conn = this.getConnection();
            PreparedStatement stat = conn.prepareStatement("insert into save_data values(null,?,?,?);");
            stat.setString(1, id);
            stat.setString(2,data);
            stat.setString(3, time);
            stat.executeUpdate();

            stat.close();
            conn.close();

            this.searchById(id);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }

    public void createThread()
    {
        tsThread = new Thread(this,"db");
        tsThread.start();
    }

    public void run()
    {
        if (tsThread != null)
        {
            this.searchAll();
        }
    }
/*
    public static void main(String args[])
    {
        DataBase_OP ts = new DataBase_OP();

//        ts.createThread();
//        ts.deleteAll();
        ts.insert("2015", "1234", "2012-04-06");
//        ts.searchById("save_data","2012");
//        ts.deleteById("2012");

    }
*/
}

index.html

<HTML>
<HEAD>
<TITLE>
test
</TITLE>
</HEAD>
<BODY>
<center>
<h3>
test
</h3>
<BR>
<APPLET
CODEBASE = "."
CODE = "CollectionInfo.class"
NAME = "collection info"
WIDTH = 600
HEIGHT = 500
HSPACE = 0
VSPACE = 0
ALIGN = middle
>
</APPLET>
</center>
</BODY>
</HTML>

运行(上面程序如果改成继承至 JFrame 运行正常)
$ appletviewer index.html  //点击查询数据时，出错

java.lang.ClassNotFoundException: org.sqlite.JDBC
java.lang.NullPointerException

解决方法：
解压 sqlite-jdbc-3.6.23.1-beta-1-SNAPSHOT.jar 到与 index.html 同一目录
或 把sqlite-jdbc-3.6.23.1-beta-1-SNAPSHOT.jar 放至$JRE_HOME/lib/ext(/usr/lib/jvm/java-6-openjdk/jre/lib/ext)

之后依次出错如下错误：
java.security.AccessControlException: access denied (java.util.PropertyPermission user.dir read)

java.security.AccessControlException: access denied (java.io.FilePermission /home/loongson/workspace/collectionInfo/src/sqlite read)

java.security.AccessControlException: access denied (java.io.FilePermission /home/loongson/workspace/collectionInfo/src/sqlite/data.db write)

解决方法：
在当前目录建立名为testPol 的文件 内容如下

grant
{
    permission java.util.PropertyPermission "user.dir", "read";
    permission java.io.FilePermission  "/home/loongson/workspace/collectionInfo/src/sqlite", "read";
    permission java.io.FilePermission  "/home/loongson/workspace/collectionInfo/src/sqlite/data.db", "write";
};

将其添加到 java.security 文件中

$ sudo vi /usr/lib/jvm/java-6-openjdk/jre/lib/security/java.security

 在
 97 policy.url.1=file:${java.home}/lib/security/java.policy
 98 policy.url.2=file:${user.home}/.java.policy  
 的下面添加
 99 policy.url.3=file:/home/loongson/workspace/collectionInfo/src/sqlite/testPol
//即将testPol 的所在路径添加到其中

$ appletviewer index.html //访问数据库正常

参考链接：


以下参考链接中的主要内容:
首先，DbaAppl.java中_url串指定的是与Web Server在同一台主机上(server1)的Oracle，编译后把DbaAppl.class和DbaAppl.html
一起放在Web Server的同一目录下，在客户机的浏览器里访问DbaAppl.html，查看Java控制台，得到如下信息

Cannot find driver
oracle.jdbc.driver.OracleDriver
java.lang.NullPointerException

这一出错信息的意思是，DriverManager 已在 applet HTML和类文件所在的目录以及当前JRE使用的本地CLASSPATH中没有找到 JDBC 驱动程序。
解决办法，将JDBC 驱动程序oracle-jdbc-driver.jar文件放在客户机当前使用的JRE的${JRE_HOME}/lib /ext目录下，或者用jar工具将oracle-jdbc-driver.jar解压，把解压后得到的oracle目录也放在Web Server上 applet所在
的目录中。

更正后，重新访问DbaAppl.html，查看Java控制台，得到如下信息

java.security.AccessControlException: access denied(java.net.SocketPermission server1 resolve)

这个错误的通知你访问被拒绝。这就是说，由于 applet 程序试 图在没有获得正确的权限的情况下访问系统资源，括号中代码表示，若要纠正这 种情况，您需要一个向数据库所在计算机（主机名为server1） 授予 applet 访问权限的 SocketPermission。


解决办法，利用 Policy 工具生成你所需要的策略文件，或者用一个 ASCII 编辑 器生成该策略文件。
下面的代码是名为 DbaApplPol 的、获得上述权限的策略文件的内容

grant {
  permission java.net.SocketPermission "server1", "resolve";
};

创建了策略文件后，还要修改${JRE_HOME}/lib/security/java.security文件
在
policy.url.1=file:${java.home}/lib/security/java.policy 
policy.url.2=file:${user.home}/.java.policy 

下添加一行
policy.url.3=file:/path/to/DbaApplPol 


再次访问DbaAppl.html，成功运行。

再将DbaAppl.java中_url串改为指定另一台主机(server2)上的Oracle，编译，上传，访问得到如下错误信息

java.security.AccessControlException: access denied(java.net.SocketPermission 10.6.1.16:1521 connect,resolve)

这回的意思是需要一个允许在数据库所在的server2上访问IP地址和端口的SocketPermission。在前面生成的策略文件中加入

permission java.net.SocketPermission "10.6.1.16:1521", "connect, resolve";

后，成功运行。

总结一下,通过applet访问数据库不是不可以的，但确实不太方便，某些特殊环境下可能值得使用。
[list=1]
[*]客户端要有JRE支持，可以在客户机访问applet所在页面是提示安装Java Plug-in,这样的页面可以通过HTMLConvert工具生成
[*]JDBC驱动要么放在客户机JRE的CLASSPATH下，要么与applet放在一起，注意如果驱动是jar文件的话，要解开
[*]通过设置正确的策略文件，applet不仅可以访问与自己在同一台机器上的数据库，也可以访问其他远程数据库。不过，需要每个想要成功运行applet的客户机修改其本地JRE的java.security文件