home.

tagged: sql

Metadata about tables in postgresql (or in information_schema in general)

  SELECT tables.table_name
FROM information_schema.tables
WHERE tables.table_schema = 'public' 
AND tables.table_name != 'schema_version' 
AND tables.table_type = 'BASE TABLE';

The above lists all the tables, excluding schema_version since we don't care about migration info and including only those in the public schema, the ones we created, excluding views by specifying the base table table type.

SELECT columns.table_name,
  columns.column_name,
  columns.data_type,
  columns.column_default,
  columns.is_nullable
 FROM information_schema.columns;

The above lists all the column names in your database, their type, the column default (you can work out if it's got a sequence etc) and if they're nullable

SELECT kcu.constraint_name,
    kcu.table_name,
    kcu.column_name 
  FROM information_schema.key_column_usage kcu
     LEFT JOIN information_schema.table_constraints tc ON tc.constraint_name = kcu.constraint_name
  WHERE tc.constraint_type = 'FOREIGN KEY';

The above lists all the foreign key constraints in your database, with the table name and column name which they belong to (not what they reference)

SELECT constraint_name, table_name, column_name 
FROM information_schema.constraint_column_usage;

The above lists all the constraints (foreign keys for example), along with the table name and column name which they reference.

postgresql sql

SQL: Renaming columns in sqlite

You can't easily do this in sqlite. Say you have a table with a schema:

CREATE TABLE ANOTHERTHING (ID NUMBER(10) NOT NULL, TEXT VARCHAR, PRIMARY KEY (ID));

And you want to rename the column 'text' to 'sometext', you need to make a new table with that schema, rename the current table, copy all the data over into the new table and then drop the renamed table:

alter table anotherthing rename to anotherthing_1;
create table anotherthing (id number(10) not null, sometext varchar, primary key (id));
insert into anotherthing(id, sometext) select id, text from anotherthing_1;
drop table anotherthing_1;

sqlite sql sql-renamecolumns

Tomcat 7: JPA using EclipseLink and Sqlite

Download EclipseLink, the reference implementation for JPA, from http://www.eclipse.org/eclipselink/downloads/ and the sqlite jdbc library, from https://bitbucket.org/xerial/sqlite-jdbc/downloads.
 
Copy, eclipselink.jar, javax_persistence_2.x.x.jar and the sqlite jdbc jar file into your tomcat lib directory, /usr/share/tomcat7/lib in my case.
 
Now restart tomcat.
 
Create persistence.xml in src/META-INF/persistence.xml.

  <?xml version="1.0" encoding="UTF-8" ?>
  <persistence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
      version="2.0"
      xmlns="http://java.sun.com/xml/ns/persistence">
    <persistence-unit name="example" transaction-type="RESOURCE_LOCAL">
      <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
      <class>org.denevell.tomcat.entities.write.AnotherThing</class>
      <properties>
        <property name="javax.persistence.jdbc.driver" value="org.sqlite.JDBC" />
        <property name="javax.persistence.jdbc.url" value="jdbc:sqlite:/var/lib/tomcat7/dbs/test.db" />
        <property name="eclipselink.logging.level" value="ALL" />
        <property name="eclipselink.ddl-generation" value="create-tables" />
      </properties>
    </persistence-unit>
 </persistence>

The persistence-unit name will be how we will grab a hold of our persistence entity manager factory. The transaction-type say we're using a local database. If it said JTA it would mean we'd have support for transactions over multiple datasources (Tomcat doesn't support this out of the box).
 
We next tell it about the provider, EclipseLink, in our case. Then the class that will be persisted. Then we set properties to tell it about our driver, our jdbc url, the logging level and how we will generate the database.
 
We could set eclipselink.ddl-generation to 'drop-and-create-tables' if we want to destroy the database everytime. In a later tutorial we'll detail how to work with an existing database without automatically doing anything, since the eclipselink.ddl-generation is only really useful during development.
 
Here's what our 'AnotherThing' entity looks like:

package org.denevell.tomcat.entities.write; // Referenced in persistence.xml

@Entity
public class AnotherThing {
  @Id @GeneratedValue
  private int id;
  private String text;
  public AnotherThing() {
  }
  public int getId() {
      return id;
  }
  public void setId(int id) {
      this.id = id;
  }
  public String getText() {
      return text;
  }
  public void setText(String text) {
      this.text = text;
  }
}

Note @Entity defines our class as just that, the @Id annotation says this is the primary key of the entity, and @GeneratedValue say the persistence provider generates its value.
 
We can now talk to our JPA instance:

// Setup the entity manager
EntityManagerFactory factory =   Persistence.createEntityManagerFactory("example");
EntityManager em = factory.createEntityManager();
// Create it
AnotherThing t = new AnotherThing();
t.setText("Heya");    	
// Add it
EntityTransaction trans = em.getTransaction();
trans.begin();
em.persist(t);
trans.commit();
// Fetch them
TypedQuery<AnotherThing> q = em.createQuery("select ting from AnotherThing ting", AnotherThing.class);
List<AnotherThing> results = q.getResultList();
for (AnotherThing thing : results) {
  writer.println(thing.getId() + ": " + thing.getText());
}
// Close the entity manager
em.close();
factory.close();

Note we're using our name of the persistence-unit above to get the entity manager factory. Then we get the entity manager (we should only have one of these). Then we create a new object as normal.
 
We then start an entity transaction (we could use this to rollback if we wanted). Then we persist the object, committing the transaction.
 
Finally we use the JPQL syntax to get all the 'AnotherThing' objects in the database. Then we close the entity manager, and the entity manager factory.
 
I did get some error about AnotherThing not being recognised. Unfortunatley I think this is a bug in Tomcat for the most part. Stopping and then starting Tomcat should resolve the problem.

java jpql jpa tomcat sql sqlite

Tomcat 7: JDBC via JNDI using sqlite

First download the sqlite jdbc driver from https://bitbucket.org/xerial/sqlite-jdbc/downloads and copy it into your lib/ folder in your tomcat directory, /usr/share/tomcat7/lib/. Then restart tomcat so it can find your new jar.
 
Then add a reference to a new jdbc resource in your web/META-INF/context.xml file. It defines a JNDI name, its class type, the driver class name that relates to the jar we just installed above, and a url to connect to the database. In my case I'm pointing to a directory (which must exist) on the file system.

<Context>
    <Resource name="jdbc/sqlite"
    type="javax.sql.DataSource"
    driverClassName="org.sqlite.JDBC"
    url="jdbc:sqlite:/var/lib/tomcat7/dbs/test.db"
    />
</Context>

In addition, you have to add the resource in your web.xml file. Note the name is the same the name above.

...
<resource-ref>
    <res-ref-name>jdbc/sqlite</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
</resource-ref>
...

Now you can make reference to the database in your Java code. This is lifted from the sqlite example page:

Connection conn = null;
try {
    Context ctx = new InitialContext();
    DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/sqlite");
    conn = ds.getConnection();
    Statement statement = conn.createStatement();
try {
    statement.executeUpdate("create table thing(x integer)");
} catch (Exception e) { 
     // Could well be already there
}
statement.executeUpdate("insert into thing values(42)");
ResultSet rs = statement.executeQuery("select * from thing");
while (rs.next()) {
writer.println("id = " + rs.getInt(1));
} catch (Exception e) {
    writer.println(e.getMessage());
} finally {
    try {
        if (conn != null) conn.close();
    } catch (SQLException e) {
        writer.println(e);
    }
}

java tomcat-jndi tomcat-jdbc jdbc sql sqlite tomcat

Page 1 of 1